﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;


namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recPaymentRepo : IRepository<recPayment, int>
    {
        public List<recPayment> GetList(int pageSize, int currentPage, string sortName, string sortOrder,string parameters)
        {
            List<recPayment> tt = new List<recPayment>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"     recPayment.PaymentID,
                                                recPayment.TransDate,
                                                recPayment.PaymentNo,
                                                recPayment.PaymentDate,
                                                recPayment.PaymentValue,
                                                recPayment.InvoiceNo,
                                                recPayment.DebtorSectorID,
                                                recPayment.PostingDate,
                                                recPayment.PostingBy,
                                                recPayment.CreatedDate,
                                                recPayment.CreatedBy,
                                                recPayment.UpdatedDate,
                                                recPayment.UpdatedBy,
                                                CASE WHEN recPayment.DebtorSectorID = 0 THEN recPayment.Remark ELSE recDebtor.DebtorName END DebtorName ,
                                                 CASE WHEN recPayment.DebtorSectorID = 0 THEN 
                                                (SELECT recSubSubSector.SubSubSectorName
                                                 FROM transactions.recSectorRegistration
                                                 LEFT JOIN master.recSubSubSector ON recSectorRegistration.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                 WHERE recSectorRegistration.InvoiceNo = recPayment.InvoiceNo
                                                 GROUP BY recSubSubSector.SubSubSectorName
                                                )ELSE recSubSubSector.SubSubSectorName END AS SubSubSectorName,
                                                recInvoice.InvoiceValue,
                                                recSector.SectorName,
                                                recSubSector.SubSectorName
");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"     transactions.recPayment 
                                                LEFT JOIN master.recDebtorSector ON recPayment.DebtorSectorID=recDebtorSector.DebtorSectorID
					                            LEFT JOIN master.recSubSubSector ON recSubSubSector.SubSubSectorID =recDebtorSector.SubSubSectorID
                                                LEFT JOIN master.recSector ON recDebtorSector.SectorID=recSector.SectorID
					                            LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID=recSubSector.SubSectorID
					                            LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID = recDebtor.DebtorID
                                                LEFT JOIN transactions.recInvoice ON recPayment.InvoiceNo = recInvoice.InvoiceNo


                    ");
                    sbQuery.AppendFormat("      {0} ",parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recPayment t = new recPayment(true);
                                t.PaymentID = dr[recPayment.Fields.PaymentID] == DBNull.Value ? 0 : int.Parse(dr[recPayment.Fields.PaymentID].ToString());
                                t.TransDate = dr[recPayment.Fields.TransDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recPayment.Fields.TransDate].ToString());
                                t.PaymentNo = dr[recPayment.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recPayment.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recPayment.Fields.PaymentDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recPayment.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recPayment.Fields.PaymentValue] == DBNull.Value ? 0 : decimal.Parse(dr[recPayment.Fields.PaymentValue].ToString());
                                t.InvoiceNo = dr[recPayment.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recPayment.Fields.InvoiceNo].ToString();
                                t.DebtorSectorID = dr[recPayment.Fields.DebtorSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recPayment.Fields.DebtorSectorID].ToString());
                                t.PostingDate = dr[recPayment.Fields.PostingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recPayment.Fields.PostingDate].ToString());
                                t.PostingBy = dr[recPayment.Fields.PostingBy] == DBNull.Value ? string.Empty : dr[recPayment.Fields.PostingBy].ToString();
                                t.CreatedDate = dr[recPayment.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recPayment.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recPayment.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recPayment.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recPayment.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recPayment.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recPayment.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recPayment.Fields.UpdatedBy].ToString();

                                t.recDebtorSector = new recDebtorSector(true);
                                t.recDebtorSector.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.recDebtorSector.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.recDebtorSector.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recDebtorSector.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                
                                t.recInvoice.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? 0 : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }


        public List<recPayment> GetListPembayaran(int pageSize, int currentPage, string sortName, string sortOrder, string queryRegistrasi, string queryPayment, string queryAksi, string parameters)
        {
            List<recPayment> tt = new List<recPayment>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"     PaymentID,
TransDate,
PaymentNo,
PaymentDate,
PaymentValue,
InvoiceNo,
DebtorSectorID,
PostingDate,
PostingBy,
CreatedDate,
CreatedBy,
UpdatedDate,
UpdatedBy,
DebtorName ,
SubSubSectorName,
InvoiceValue,
SectorName,
SubSectorName,
UserID
FROM (");
                    sbQuery.AppendFormat(queryRegistrasi);
                    sbQuery.AppendFormat(" UNION");
                    sbQuery.AppendFormat(queryPayment);
                    if (queryAksi.Trim().Length > 1)
                    {
                        sbQuery.AppendFormat(" UNION");
                        sbQuery.AppendFormat(queryAksi);
                    }
                    sbQuery.AppendFormat(") recPayment");
                    sbQuery.AppendFormat(" {0}", parameters);
                  
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recPayment t = new recPayment(true);
                                t.PaymentID = dr[recPayment.Fields.PaymentID] == DBNull.Value ? 0 : int.Parse(dr[recPayment.Fields.PaymentID].ToString());
                                t.TransDate = dr[recPayment.Fields.TransDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recPayment.Fields.TransDate].ToString());
                                t.PaymentNo = dr[recPayment.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recPayment.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recPayment.Fields.PaymentDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recPayment.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recPayment.Fields.PaymentValue] == DBNull.Value ? 0 : decimal.Parse(dr[recPayment.Fields.PaymentValue].ToString());
                                t.InvoiceNo = dr[recPayment.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recPayment.Fields.InvoiceNo].ToString();
                                t.DebtorSectorID = dr[recPayment.Fields.DebtorSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recPayment.Fields.DebtorSectorID].ToString());
                                t.PostingDate = dr[recPayment.Fields.PostingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recPayment.Fields.PostingDate].ToString());
                                t.PostingBy = dr[recPayment.Fields.PostingBy] == DBNull.Value ? string.Empty : dr[recPayment.Fields.PostingBy].ToString();
                                t.CreatedDate = dr[recPayment.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recPayment.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recPayment.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recPayment.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recPayment.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recPayment.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recPayment.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recPayment.Fields.UpdatedBy].ToString();

                                t.recDebtorSector = new recDebtorSector(true);
                                t.recDebtorSector.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.recDebtorSector.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.recDebtorSector.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recDebtorSector.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();

                                t.recInvoice.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? 0 : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public object SelectScalarPembayaran(SQL.Function.Aggregate function, string column, string queryRegistrasi, string queryPayment, string queryAksi, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                       
                        case SQL.Function.Aggregate.Count:
                                    sbQuery.AppendFormat("SELECT COUNT({0}) FROM ( ",column);
                                    sbQuery.AppendFormat(queryRegistrasi);
                                    sbQuery.AppendFormat("  UNION");
                                    sbQuery.AppendFormat(queryPayment);
                                    if (queryAksi.Trim().Length > 1)
                                    {
                                    sbQuery.AppendFormat("  UNION");
                                    sbQuery.AppendFormat(queryAksi);
                                    }
                                    sbQuery.AppendFormat(") recPayment");
                                    sbQuery.AppendFormat(" {0}", parameters);

                                                //transactions.recPayment LEFT JOIN master.recDebtorSector ON recPayment.DebtorSectorID=recDebtorSector.DebtorSectorID
                                                //LEFT JOIN master.recSubSubSector ON recSubSubSector.SubSubSectorID =recDebtorSector.SubSubSectorID
                                                //LEFT JOIN master.recSector ON recDebtorSector.SectorID=recSector.SectorID
                                                //LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID=recSubSector.SubSectorID
                                                //LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID = recDebtor.DebtorID
                                                //LEFT JOIN transactions.recInvoice ON recPayment.InvoiceNo = recInvoice.InvoiceNo {1} ", column, parameters);
                            break;
                       
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public int GetMaxID()
        {
            int result = 1;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                begin
                                    declare @max int = 1;

                                    if Exists(SELECT MAX(CONVERT(INT,SUBSTRING(PaymentNo,9,6))) BillingNo FROM transactions.recPayment where SUBSTRING(PaymentNo,3,2) = MONTH(GETDATE()))
	                                    set @max = (SELECT MAX(CONVERT(INT,SUBSTRING(PaymentNo,9,6)))+1 recBilling FROM transactions.recPayment where SUBSTRING(PaymentNo,3,2) = MONTH(GETDATE()));
                                    else
	                                    set @max = 1;

                                    select @max;	
                                end ";
                    cmd.CommandType = CommandType.Text;

                    try
                    {
                        conn.Open();
                        var objId = cmd.ExecuteScalar();
                        result = objId == DBNull.Value ? 1 : int.Parse(objId.ToString());
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }

        public string GetPaymentIDByParam(int Param)
        {
            string actionName = string.Empty;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"DECLARE    @return_value int,
                                        @hasil varchar(100)
                                        EXEC    @return_value = [dbo].[SP_GenTransCode]
                                        @kode = N'"+Param+"', @hasil = @hasil OUTPUT SELECT    @hasil as N'@hasil'";
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                actionName = dr[0] == DBNull.Value ? string.Empty : dr[0].ToString();


                            }   
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return actionName;
        }

        private DataTable GetDataRecBilling(string InvoiceNo)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                                           string getBilling = @"SELECT BillingNo
                                            ,DebtorSectorID
                                            ,TransTypeID
                                            ,TariffID
                                            ,Value
                                            ,InvoiceNo
                                            ,InvoiceDetailID
                                            ,Balance
                                            ,PrevBillingNo
                                            ,BillingDate
                                            ,DueDate
                                            ,IsCancel
                                            ,ForPayment
                                            ,SaikoNo
                                            ,CreatedDate
                                            ,CreatedBy
                                            ,UpdatedDate
                                            ,UpdatedBy
                                            ,CalculationID
                                            ,PaymentValue
                                            FROM transactions.recBilling
                                            WHERE InvoiceNo=@InvoiceNo
                                            ORDER BY DueDate ASC
";

                       
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = InvoiceNo;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = getBilling;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;
        }

        private DataTable GetDataRecInvoice(string InvoiceNo)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string getInvoice = @" SELECT InvoiceNo
                                          ,InvoiceDate
                                          ,InvoiceValue
                                          ,BankID
                                          ,BeSpelled
                                          ,PaymentNo
                                          ,PaymentDate
                                          ,PaymentValue
                                          ,IsHapusBuku
                                          ,IsHapusTagih
                                          ,DebtorSectorID
                                          ,CreatedDate
                                          ,CreatedBy
                                          ,UpdatedDate
                                          ,UpdatedBy
                                          ,IsCancel
                                          ,Balance
                                      FROM transactions.recInvoice
                                      WHERE InvoiceNo=@InvoiceNo";                               


                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = InvoiceNo;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = getInvoice;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;
        }

        private string GetTransTypeName(int transTypeID)
        {
            string result = string.Empty;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string queryTransType = @"SELECT TransTypeName FROM master.recTransactionType WHERE TransTypeID=@TransTypeID";
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.Int).Value = transTypeID;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = queryTransType;


                    try
                    {
                        conn.Open();
                        object results = cmd.ExecuteScalar();
                        result = Convert.ToString(results);
                        //da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
                
            }
            return result;
        }

        public bool SavePembayaran(recPayment domain)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
  
                     #region Insert Data Payment

                    #region Generate PaymentNo dari Stored Procedure

                    SqlCommand cmdGeneratePaymentNo = conn.CreateCommand();
                    cmdGeneratePaymentNo.CommandText = "[dbo].[SP_GenTransCode]";
                    cmdGeneratePaymentNo.CommandType = CommandType.StoredProcedure;
                    cmdGeneratePaymentNo.Parameters.Add("@kode", SqlDbType.VarChar).Value = "8";
                    SqlParameter prmResultPaymentNo = new SqlParameter();
                    prmResultPaymentNo.ParameterName = "@hasil";
                    prmResultPaymentNo.Direction = ParameterDirection.Output;
                    prmResultPaymentNo.SqlDbType = SqlDbType.VarChar;
                    prmResultPaymentNo.Size = 100;
                    cmdGeneratePaymentNo.Parameters.Add(prmResultPaymentNo);
                    cmdGeneratePaymentNo.Transaction = txn;
                    cmdGeneratePaymentNo.ExecuteNonQuery();
                    cmdGeneratePaymentNo.Dispose();

                    var GenPaymentNo = prmResultPaymentNo.Value;

                    domain.PaymentNo = GenPaymentNo.ToString().Trim();

                    #endregion

                    string queryPayment = @"INSERT INTO transactions.recPayment(
                                                TransDate,
                                                PaymentNo,
                                                PaymentDate,
                                                PaymentValue,
                                                InvoiceNo,
                                                DebtorSectorID,
                                                PostingDate,
                                                PostingBy,
                                                CreatedDate,
                                                CreatedBy,
                                                UpdatedDate,
                                                UpdatedBy,
                                                Remark
                                                ) 
                                                VALUES(
                                                @TransDate,
                                                @PaymentNo,
                                                @PaymentDate,
                                                @PaymentValue,
                                                @InvoiceNo,
                                                @DebtorSectorID,
                                                @PostingDate,
                                                @PostingBy,
                                                @CreatedDate,
                                                @CreatedBy,
                                                @UpdatedDate,
                                                @UpdatedBy,
                                                @Remark
                                                )";

                        SqlCommand cmd = new SqlCommand(queryPayment, conn);
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("@TransDate", SqlDbType.Date).Value = domain.TransDate;
                        cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                        cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                        cmd.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = domain.PaymentValue;
                        cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                        cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)domain.DebtorSectorID ?? DBNull.Value;
                        cmd.Parameters.Add("@PostingDate", SqlDbType.VarChar).Value = (object)domain.PostingDate ?? DBNull.Value;
                        cmd.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)domain.PostingBy ?? DBNull.Value;
                        cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                        cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                        cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                        cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                        cmd.Parameters.Add("@Remark", SqlDbType.VarChar).Value = (object)domain.Remark ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();

                    #endregion

                     #region Update Data recInvoice
                        
                        #region Ambil InvoiceValue berdasar InvoiceNo

                        string strInvoiceValue = @"SELECT InvoiceValue  FROM transactions.recInvoice WHERE InvoiceNo=@InvoiceNo";
                        SqlCommand cmdInvoiceValue = new SqlCommand(strInvoiceValue, conn);
                        cmdInvoiceValue.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                        cmdInvoiceValue.Transaction = txn;
                        decimal InvoiceValue = Convert.ToDecimal(cmdInvoiceValue.ExecuteScalar());
                        cmdInvoiceValue.Dispose();

                        #endregion

                        #region Query Update recInvoice

                        string queryInvoice = @"UPDATE  transactions.recInvoice SET PaymentNo=@PaymentNo,
                                            PaymentDate=@PaymentDate,
                                            PaymentValue=@PaymentValue,
                                            Balance=@Balance
                                            WHERE InvoiceNo=@InvoiceNo";

                            SqlCommand cmdInvoice = new SqlCommand(queryInvoice, conn);
                            cmdInvoice.CommandType = CommandType.Text;

                            cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                            cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                            cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = domain.PaymentValue;
                            cmdInvoice.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// InvoiceValue - domain.PaymentValue; 
                            cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                            cmdInvoice.Transaction = txn;
                            cmdInvoice.ExecuteNonQuery();
                            cmdInvoice.Dispose();

                        #endregion
                         

                        #endregion

                     #region UPDATE recBilling
                        
                            //DataTable dt = GetDataRecBilling(domain.InvoiceNo);

                            #region Ambil Data Billing Berdasar InvoiceNo

                            string getdataRecBilling = @"SELECT BillingNo
                                                    ,DebtorSectorID
                                                    ,TransTypeID
                                                    ,TariffID
                                                    ,Value
                                                    ,InvoiceNo
                                                    ,InvoiceDetailID
                                                    ,Balance
                                                    ,PrevBillingNo
                                                    ,BillingDate
                                                    ,ISNULL(DueDate,'01/01/1900') DueDate
                                                    ,IsCancel
                                                    ,ForPayment
                                                    ,SaikoNo
                                                    ,CreatedDate
                                                    ,CreatedBy
                                                    ,UpdatedDate
                                                    ,UpdatedBy
                                                    ,CalculationID
                                                    ,PaymentValue
                                                    ,IsManual
                                                    ,IsHold
                                                    ,IsPosting
                                                    ,ObjectionNo
                                                FROM transactions.recBilling WHERE InvoiceNo=@InvoiceNo";
                            SqlCommand cmdDataBilling = new SqlCommand(getdataRecBilling, conn);
                            cmdDataBilling.CommandType = CommandType.Text;
                            cmdDataBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                            cmdDataBilling.Transaction = txn;
                            SqlDataAdapter da = new SqlDataAdapter();
                            DataTable dtDataBilling = new DataTable();
                            da.SelectCommand = cmdDataBilling;
                            da.Fill(dtDataBilling);
                            cmdDataBilling.Dispose();

                            #endregion

                            #region Update Semua Balance recBilling jadi 0
                            foreach (DataRow row in dtDataBilling.Rows)
                            {
                                string updateBalanceRecBilling = "UPDATE transactions.recBilling SET Balance = @Balance WHERE BillingNo = @BillingNo";

                                SqlCommand cmdUpdateBalanceBilling = new SqlCommand(updateBalanceRecBilling, conn);
                                cmdUpdateBalanceBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim() ;
                                cmdUpdateBalanceBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;
                                

                                cmdUpdateBalanceBilling.Transaction = txn;
                                cmdUpdateBalanceBilling.ExecuteNonQuery();
                                cmdUpdateBalanceBilling.Dispose();
                            }
                            #endregion

                            if (dtDataBilling.Rows.Count == 1)
                            {
                                #region Jika Data Billing Jumlahnya 1
                                foreach (DataRow row in dtDataBilling.Rows)
                                {
                                    string queryUpdateBilling = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo";

                                    SqlCommand cmdUpdateBilling = new SqlCommand(queryUpdateBilling, conn);
                                    cmdUpdateBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = domain.PaymentValue;// == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.PaymentValue].ToString());
                                    decimal value = row[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.Value].ToString());
                                    cmdUpdateBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// value - domain.PaymentValue;
                                    cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();

                                    cmdUpdateBilling.Transaction = txn;
                                    cmdUpdateBilling.ExecuteNonQuery();
                                    cmdUpdateBilling.Dispose();

                                }

                                #endregion

                            }

                            else if (dtDataBilling.Rows.Count > 1)
                            {
                                #region Jika Jumlah Data Billing Lebih Dari 1
                                decimal tempPaymentValue = domain.PaymentValue;
                                foreach (DataRow row in dtDataBilling.Rows)
                                {
                                    if (tempPaymentValue >= Convert.ToDecimal(row["Value"]))
                                    {
                                        string queryUpdateBilling = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo AND DueDate=@DueDate";

                                        SqlCommand cmdUpdateBilling = new SqlCommand(queryUpdateBilling, conn);
                                        cmdUpdateBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = Convert.ToDecimal(row["Value"]);// row[recBilling.Fields.PaymentValue] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.PaymentValue].ToString());
                                        cmdUpdateBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;
                                        //cmdUpdateBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                                        cmdUpdateBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(row["DueDate"]);
                                        cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();

                                        cmdUpdateBilling.Transaction = txn;
                                        cmdUpdateBilling.ExecuteNonQuery();
                                        cmdUpdateBilling.Dispose();


                                    }
                                    else
                                    {
                                        string queryUpdateBilling = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo AND DueDate=@DueDate";

                                        SqlCommand cmdUpdateBilling = new SqlCommand(queryUpdateBilling, conn);
                                        cmdUpdateBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = tempPaymentValue;// row[recBilling.Fields.PaymentValue] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.PaymentValue].ToString());
                                        cmdUpdateBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// Convert.ToDecimal(row["Value"]) - tempPaymentValue;
                                        //cmdUpdateBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                                        cmdUpdateBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(row["DueDate"]);
                                        cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();

                                        cmdUpdateBilling.Transaction = txn;
                                        cmdUpdateBilling.ExecuteNonQuery();
                                        cmdUpdateBilling.Dispose();
                                    }

                                    tempPaymentValue = tempPaymentValue - Convert.ToDecimal(row["Value"]);

                                    if (tempPaymentValue == 0 || tempPaymentValue < 0)
                                        break;
                                }
                                #endregion
                            }
                        
                       
                        

                        #endregion

                     #region Update recSanksi recRegistration recActionCorporation

                     foreach (DataRow row in dtDataBilling.Rows)
                       {

                           string transTypeName = GetTransTypeName(Convert.ToInt16(row["TransTypeID"]));

                           if (transTypeName.Trim().ToLower().Contains("sanksi"))
                           {
                               string updateSanksi = @"UPDATE transactions.recSanction SET PaymentNo=@PaymentNo,PaymentDate=@PaymentDate WHERE InvoiceNo=@InvoiceNo";
                               SqlCommand cmdSanction = new SqlCommand(updateSanksi, conn);
                               cmdSanction.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                               cmdSanction.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                               cmdSanction.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value =  domain.InvoiceNo;

                               cmdSanction.Transaction = txn;
                               cmdSanction.ExecuteNonQuery();
                               cmdSanction.Dispose();


                           }
                           else if (transTypeName.Trim().ToLower().Contains("registrasi"))
                           {
                               string updateRegistration = @"UPDATE transactions.recSectorRegistration SET PaymentNo=@PaymentNo,PaymentDate=@PaymentDate,PaymentValue=@PaymentValue,RegistrationStatusID=@RegistrationStatusID WHERE InvoiceNo=@InvoiceNo";
                               SqlCommand cmdRegistration = new SqlCommand(updateRegistration, conn);
                               cmdRegistration.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                               cmdRegistration.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                               cmdRegistration.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = domain.PaymentValue;
                               cmdRegistration.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                               cmdRegistration.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = 2;

                               cmdRegistration.Transaction = txn;
                               cmdRegistration.ExecuteNonQuery();
                               cmdRegistration.Dispose();
                           }
                           else if (transTypeName.Trim().ToLower().Contains("aksi korporasi"))
                           {
                               string updateAksi = @"UPDATE transactions.recCorporateAction SET PaymentNo=@PaymentNo,PaymentDate=@PaymentDate WHERE InvoiceNo=@InvoiceNo";
                               SqlCommand cmdAksi = new SqlCommand(updateAksi, conn);
                               cmdAksi.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                               cmdAksi.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                               cmdAksi.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                               cmdAksi.Transaction = txn;
                               cmdAksi.ExecuteNonQuery();
                               cmdAksi.Dispose();
                           }

                           
                           
                       }

                       #endregion

                       
                     #region Lebih Bayar
                             if (InvoiceValue - domain.PaymentValue < 0)
                             {
                                 #region Ambil TransTypeID dengan Kode 10

                                 string strTransTypeID = @"SELECT TransTypeID FROM master.recTransactionType WHERE TransTypeCode=@TransTypeCode";


                                 SqlCommand cmdTransTypeID = new SqlCommand(strTransTypeID, conn);
                                 cmdTransTypeID.Parameters.Add("@TransTypeCode", SqlDbType.VarChar).Value = "10";
                                 cmdTransTypeID.Transaction = txn;
                                 var TransTypeID = cmdTransTypeID.ExecuteScalar();
                                 int resultTransTypeID = TransTypeID == DBNull.Value ? 0 : int.Parse(TransTypeID.ToString());
                                 cmdTransTypeID.Dispose();

                                 #endregion

                                 #region Generate BillingNo dari SP untuk lebih Bayar

                                 SqlCommand cmdGenerateBillingLebih = conn.CreateCommand();
                                 cmdGenerateBillingLebih.CommandText = "[dbo].[SP_GenTransCode]";
                                 cmdGenerateBillingLebih.CommandType = CommandType.StoredProcedure;
                                 cmdGenerateBillingLebih.Parameters.Add("@kode", SqlDbType.VarChar).Value = "10";
                                 SqlParameter prmResult = new SqlParameter();
                                 prmResult.ParameterName = "@hasil";
                                 prmResult.Direction = ParameterDirection.Output;
                                 prmResult.SqlDbType = SqlDbType.VarChar;
                                 prmResult.Size = 100;
                                 cmdGenerateBillingLebih.Parameters.Add(prmResult);
                                 cmdGenerateBillingLebih.Transaction = txn;
                                 cmdGenerateBillingLebih.ExecuteNonQuery();
                                 cmdGenerateBillingLebih.Dispose();

                                 var BillingNoLebihBayar = prmResult.Value;

                                 #endregion

                                 #region Get TermDay buat DebitNote dengan Kode 11

                                 string queryDebitNote = " SELECT TermDay FROM master.recTransactionType  WHERE TransTypeCode=@TransTypeCode";

                                 SqlCommand cmdDebitNote = new SqlCommand(queryDebitNote, conn);
                                 cmdDebitNote.CommandType = CommandType.Text;
                                 cmdDebitNote.Parameters.Add("@TransTypeCode", SqlDbType.Int).Value = 11;

                                 cmdDebitNote.Transaction = txn;
                                 object termDay = cmdDebitNote.ExecuteScalar();
                                 cmdDebitNote.Dispose();

                                 #endregion

                                 #region Get DueDate Dari Function  Buat Debit Note

                                 SqlCommand cmdSPDueDate = new SqlCommand("SELECT dbo.FN_CekDueDate(@Param1) DueDate", conn);
                                 cmdSPDueDate.CommandType = CommandType.Text;
                                 cmdSPDueDate.Parameters.Add("@Param1", SqlDbType.VarChar).Value = domain.PaymentDate.AddDays(Convert.ToInt32(termDay)).ToString("yyyyMMdd");
                                 cmdSPDueDate.Transaction = txn;
                                 object DuedateDebitNote = cmdSPDueDate.ExecuteScalar();
                                 cmdSPDueDate.Dispose();


                                 #endregion

                                 string InvoiceNoLebihBayar = string.Empty;
                                 int debtorsectorID = 0;

                                 for (int i = 0; i< dtDataBilling.Rows.Count; i++)
                                 {
                                     InvoiceNoLebihBayar = dtDataBilling.Rows[i]["BillingNo"].ToString();
                                     debtorsectorID = Convert.ToInt32(dtDataBilling.Rows[i]["DebtorSectorID"]);
                                 }

                        
                                 #region Query Insert recBilling Lebih Bayar
                                 string queryLebihBayar = @"INSERT INTO  transactions.recBilling (
                                                    TransTypeID,
                                                    BillingNo,
                                                    Value,
                                                    TariffID,
                                                    ForPayment,
                                                    PrevBillingNo,
                                                    DebtorSectorID,
                                                    BillingDate,
                                                    CreatedDate,
                                                    CreatedBy,
                                                    DueDate,
                                                    Balance
                                                    )
                                                    VALUES
                                                    (
                                                    @TransTypeID,
                                                    @BillingNo,
                                                    @Value,
                                                    NULL,
                                                    @ForPayment,
                                                    @PrevBillingNo,
                                                    @DebtorSectorID,
                                                    @BillingDate,
                                                    @CreatedDate,
                                                    @CreatedBy,
                                                    NULL,
                                                    @Balance
                                                    )
                                                    ";
                                
                                 
                                 SqlCommand cmdLebihBayar = new SqlCommand(queryLebihBayar, conn);
                                 cmdLebihBayar.CommandType = CommandType.Text;

                                 cmdLebihBayar.Parameters.Add("@TransTypeID", SqlDbType.Int).Value = resultTransTypeID;
                                 cmdLebihBayar.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = BillingNoLebihBayar.ToString().Trim(); //"10." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + BillingNoLebihBayar.ToString().PadLeft(5, '0');
                                 cmdLebihBayar.Parameters.Add("@Value", SqlDbType.Money).Value = InvoiceValue - domain.PaymentValue;
                                 cmdLebihBayar.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = "Lebih bayar dari No. Surat Setoran "+domain.InvoiceNo;
                                 cmdLebihBayar.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = InvoiceNoLebihBayar;
                                 cmdLebihBayar.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorsectorID;
                                 cmdLebihBayar.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = DateTime.Now;
                                 cmdLebihBayar.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                                 cmdLebihBayar.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                                 //cmdLebihBayar.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(DuedateDebitNote);
                                 cmdLebihBayar.Parameters.Add("@Balance", SqlDbType.Decimal).Value =InvoiceValue-domain.PaymentValue;

                                 cmdLebihBayar.Transaction = txn;
                                 cmdLebihBayar.ExecuteNonQuery();
                                 cmdLebihBayar.Dispose();

                                 #endregion
                             }
                     #endregion

                     #region Kurang Bayar


                        if (InvoiceValue - domain.PaymentValue > 0)
                        {

                            #region Ambil TransTypeID dengan Kode 11

                            string strTransTypeID2 = @"SELECT TransTypeID FROM master.recTransactionType WHERE TransTypeCode=@TransTypeCode";


                            SqlCommand cmdTransTypeID2 = new SqlCommand(strTransTypeID2, conn);
                            cmdTransTypeID2.Parameters.Add("@TransTypeCode", SqlDbType.VarChar).Value = "11";
                            cmdTransTypeID2.Transaction = txn;
                            var TransTypeID2 = cmdTransTypeID2.ExecuteScalar();
                            int resultTransTypeID2 = TransTypeID2 == DBNull.Value ? 0 : int.Parse(TransTypeID2.ToString());
                            cmdTransTypeID2.Dispose();

                            #endregion

                            #region Generate BillingNo dari SP untuk Kurang Bayar

                            SqlCommand cmdGenerateBillingKurang = conn.CreateCommand();
                            cmdGenerateBillingKurang.CommandText = "[dbo].[SP_GenTransCode]";
                            cmdGenerateBillingKurang.CommandType = CommandType.StoredProcedure;
                            cmdGenerateBillingKurang.Parameters.Add("@kode", SqlDbType.VarChar).Value = "11";
                            SqlParameter prmResultKurang = new SqlParameter();
                            prmResultKurang.ParameterName = "@hasil";
                            prmResultKurang.Direction = ParameterDirection.Output;
                            prmResultKurang.SqlDbType = SqlDbType.VarChar;
                            prmResultKurang.Size = 100;
                            cmdGenerateBillingKurang.Parameters.Add(prmResultKurang);
                            cmdGenerateBillingKurang.Transaction = txn;
                            cmdGenerateBillingKurang.ExecuteNonQuery();
                            cmdGenerateBillingKurang.Dispose();

                            var BillingNoKurangBayar = prmResultKurang.Value;

                            #endregion

                            #region Get TermDay buat CreditNote dengan Kode 10

                            string queryCreditNote = " SELECT TermDay FROM master.recTransactionType  WHERE TransTypeCode=@TransTypeCode";

                            SqlCommand cmdCreditNote = new SqlCommand(queryCreditNote, conn);
                            cmdCreditNote.CommandType = CommandType.Text;
                            cmdCreditNote.Parameters.Add("@TransTypeCode", SqlDbType.Int).Value = 10 ;

                            cmdCreditNote.Transaction = txn;
                            object termDay =  cmdCreditNote.ExecuteScalar();
                            cmdCreditNote.Dispose();

                            #endregion

                            #region Get DueDate Dari Function  Buat Credit Note

                            SqlCommand cmdSPDueDate = new SqlCommand("SELECT dbo.FN_CekDueDate(@Param1) DueDate", conn);
                            cmdSPDueDate.CommandType = CommandType.Text;
                            cmdSPDueDate.Parameters.Add("@Param1", SqlDbType.VarChar).Value = domain.PaymentDate.AddDays(Convert.ToInt32(termDay)).ToString("yyyyMMdd");
                            cmdSPDueDate.Transaction = txn;
                            object DuedateCreditNote = cmdSPDueDate.ExecuteScalar();
                            cmdSPDueDate.Dispose();


                            #endregion

                            decimal tempValue = domain.PaymentValue;
                            string InvoiceNoKurangBayar = string.Empty;
                            int debtorsectorID = 0;
                            foreach (DataRow row in dtDataBilling.Rows)
                            {
                                tempValue = tempValue - Convert.ToDecimal(row["Value"]);
                                InvoiceNoKurangBayar = row["BillingNo"].ToString();
                                debtorsectorID = Convert.ToInt32(row["DebtorSectorID"]);
                                if (tempValue == 0 || tempValue < 0)
                                    break;
                            }

                            #region Query Insert recBilling Lebih Bayar
                            string queryKurangBayar = @"INSERT INTO  transactions.recBilling (
                                                    TransTypeID,
                                                    BillingNo,
                                                    Value,
                                                    TariffID,
                                                    ForPayment,
                                                    PrevBillingNo,
                                                    DebtorSectorID,
                                                    BillingDate,
                                                    CreatedDate,
                                                    CreatedBy,
                                                    DueDate,
                                                    Balance
                                                    )
                                                    VALUES
                                                    (
                                                    @TransTypeID,
                                                    @BillingNo,
                                                    @Value,
                                                    NULL,
                                                    @ForPayment,
                                                    @PrevBillingNo,
                                                    @DebtorSectorID,
                                                    @BillingDate,
                                                    @CreatedDate,
                                                    @CreatedBy,
                                                    @DueDate,
                                                    @Balance

                                                    )
                                                    ";
                            

                            SqlCommand cmdKurangBayar = new SqlCommand(queryKurangBayar, conn);
                            cmdKurangBayar.CommandType = CommandType.Text;

                            cmdKurangBayar.Parameters.Add("@TransTypeID", SqlDbType.Int).Value = resultTransTypeID2;
                            cmdKurangBayar.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = BillingNoKurangBayar.ToString().Trim(); //"11." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + BillingNoKurangBayar.ToString().PadLeft(5, '0');
                            cmdKurangBayar.Parameters.Add("@Value", SqlDbType.Money).Value = InvoiceValue - domain.PaymentValue;
                            cmdKurangBayar.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = "Kurang bayar dari No. Surat Setoran " + domain.InvoiceNo;
                            cmdKurangBayar.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = InvoiceNoKurangBayar;
                            cmdKurangBayar.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorsectorID;
                            cmdKurangBayar.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = DateTime.Now;
                            cmdKurangBayar.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                            cmdKurangBayar.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                            cmdKurangBayar.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(DuedateCreditNote);
                            cmdKurangBayar.Parameters.Add("@Balance", SqlDbType.Decimal).Value = InvoiceValue - domain.PaymentValue;

                            cmdKurangBayar.Transaction = txn;
                            cmdKurangBayar.ExecuteNonQuery();
                            cmdKurangBayar.Dispose();
                            #endregion
                        }
                        #endregion

                        result = true;
                        txn.Commit();

                    }
                    catch (Exception ex)
                    {
                        txn.Rollback();
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                //}
            }
            return result;
        }

        public bool EditPembayaran(recPayment domain)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {

                    #region UPDATE Data Payment
                    string queryPayment = @"UPDATE transactions.recPayment SET
                                                TransDate=@TransDate,
                                                PaymentDate=@PaymentDate,
                                                PaymentValue=@PaymentValue,
                                                InvoiceNo=@InvoiceNo,
                                                DebtorSectorID=@DebtorSectorID,
                                                UpdatedDate = @UpdatedDate,
                                                UpdatedBy = @UpdatedBy,
                                                Remark=@Remark
                                                WHERE PaymentNo=@PaymentNo 
                                               ";

                    SqlCommand cmd = new SqlCommand(queryPayment, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@TransDate", SqlDbType.Date).Value = domain.TransDate;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = domain.PaymentValue;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)domain.DebtorSectorID ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@Remark", SqlDbType.VarChar).Value = (object)domain.Remark ?? DBNull.Value;


                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion

                    #region Update Data recInvoice

                    #region Ambil InvoiceValue berdasar InvoiceNo

                    string strInvoiceValue = @"SELECT InvoiceValue  FROM transactions.recInvoice WHERE InvoiceNo=@InvoiceNo";
                    SqlCommand cmdInvoiceValue = new SqlCommand(strInvoiceValue, conn);
                    cmdInvoiceValue.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                    cmdInvoiceValue.Transaction = txn;
                    decimal InvoiceValue = Convert.ToDecimal(cmdInvoiceValue.ExecuteScalar());
                    cmdInvoiceValue.Dispose();

                    #endregion

                    #region Query Update recInvoice

                    string queryInvoice = @"UPDATE  transactions.recInvoice SET PaymentNo=@PaymentNo,
                                            PaymentDate=@PaymentDate,
                                            PaymentValue=@PaymentValue,
                                            Balance=@Balance
                                            WHERE InvoiceNo=@InvoiceNo";

                    SqlCommand cmdInvoice = new SqlCommand(queryInvoice, conn);
                    cmdInvoice.CommandType = CommandType.Text;

                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = domain.PaymentValue;
                    cmdInvoice.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// InvoiceValue - domain.PaymentValue; //domainInvoice.InvoiceValue - domain.PaymentValue;
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();
                    cmdInvoice.Dispose();

                    #endregion


                    #endregion

                    #region UPDATE recBilling

                    //DataTable dt = GetDataRecBilling(domain.InvoiceNo);

                    #region Ambil Data Billing Berdasar InvoiceNo

                    string getdataRecBilling = "SELECT * FROM transactions.recBilling WHERE InvoiceNo=@InvoiceNo";
                    SqlCommand cmdDataBilling = new SqlCommand(getdataRecBilling, conn);
                    cmdDataBilling.CommandType = CommandType.Text;
                    cmdDataBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                    cmdDataBilling.Transaction = txn;
                    SqlDataAdapter da = new SqlDataAdapter();
                    DataTable dtDataBilling = new DataTable();
                    da.SelectCommand = cmdDataBilling;
                    da.Fill(dtDataBilling);
                    cmdDataBilling.Dispose();

                    #endregion

                    #region Update recBilling dengan PaymentValue = 0

                    foreach (DataRow row in dtDataBilling.Rows)
                    {

                        string queryUpdateBillingPayment = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo";

                        SqlCommand cmdUpdateBillingPayment = new SqlCommand(queryUpdateBillingPayment, conn);
                        cmdUpdateBillingPayment.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = 0;
                        decimal value = row[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.Value].ToString());
                        cmdUpdateBillingPayment.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// value;
                        cmdUpdateBillingPayment.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();

                        cmdUpdateBillingPayment.Transaction = txn;
                        cmdUpdateBillingPayment.ExecuteNonQuery();
                        cmdUpdateBillingPayment.Dispose();

                    }

                    #endregion

                    #region Update Semua Balance recBilling jadi 0
                    foreach (DataRow row in dtDataBilling.Rows)
                    {
                        string updateBalanceRecBilling = "UPDATE transactions.recBilling SET Balance = @Balance WHERE BillingNo = @BillingNo";

                        SqlCommand cmdUpdateBalanceBilling = new SqlCommand(updateBalanceRecBilling, conn);
                        cmdUpdateBalanceBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();
                        cmdUpdateBalanceBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;


                        cmdUpdateBalanceBilling.Transaction = txn;
                        cmdUpdateBalanceBilling.ExecuteNonQuery();
                        cmdUpdateBalanceBilling.Dispose();
                    }
                    #endregion

                    if (dtDataBilling.Rows.Count == 1)
                    {
                        #region Jika Data Billing Jumlahnya 1
                        foreach (DataRow row in dtDataBilling.Rows)
                        {
                            string queryUpdateBilling = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo";

                            SqlCommand cmdUpdateBilling = new SqlCommand(queryUpdateBilling, conn);
                            cmdUpdateBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = domain.PaymentValue;// == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.PaymentValue].ToString());
                            decimal value = row[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.Value].ToString());
                            cmdUpdateBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// value - domain.PaymentValue;
                            //cmdUpdateBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                            cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();

                            cmdUpdateBilling.Transaction = txn;
                            cmdUpdateBilling.ExecuteNonQuery();
                            cmdUpdateBilling.Dispose();


                            #region Ambil Data PrevBillingNo Berdasar BillingNo

                            string GetBillingNo = row["BillingNo"] == DBNull.Value ? string.Empty : row["BillingNo"].ToString();
                            string queryGetPrevBilling = " SELECT COUNT(*) FROM transactions.recBilling WHERE  PrevBillingNo = @BillingNo";
                            SqlCommand cmdGetPrevBilling = new SqlCommand(queryGetPrevBilling, conn);
                            cmdGetPrevBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = GetBillingNo;

                            cmdGetPrevBilling.Transaction = txn;
                            object jumlahData = cmdGetPrevBilling.ExecuteScalar();
                            int jumlahPrevBilling = jumlahData == null ? 0 : Convert.ToInt32(jumlahData);
                            cmdGetPrevBilling.Dispose();

                            #endregion

                            #region Delete Data recBilling Berdasar PrevBilling

                            if (jumlahPrevBilling > 0 )
                            {
                                string queryDeletePrevBilling = "DELETE FROM transactions.recBilling WHERE  PrevBillingNo = @PrevBillingNo";
                                SqlCommand cmdDeletePrevBilling = new SqlCommand(queryDeletePrevBilling, conn);
                                cmdDeletePrevBilling.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = GetBillingNo;

                                cmdDeletePrevBilling.Transaction = txn;
                                cmdDeletePrevBilling.ExecuteNonQuery();
                                cmdDeletePrevBilling.Dispose();

                            }

                            #endregion

                        }

                        #endregion

                    }

                    else if (dtDataBilling.Rows.Count > 1)
                    {
                        #region Jika Jumlah Data Billing Lebih Dari 1
                        decimal tempPaymentValue = domain.PaymentValue;
                        foreach (DataRow row in dtDataBilling.Rows)
                        {
                            if (tempPaymentValue >= Convert.ToDecimal(row["Value"]))
                            {
                                string queryUpdateBilling = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo AND DueDate=@DueDate";

                                SqlCommand cmdUpdateBilling = new SqlCommand(queryUpdateBilling, conn);
                                cmdUpdateBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = Convert.ToDecimal(row["Value"]);// row[recBilling.Fields.PaymentValue] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.PaymentValue].ToString());
                                cmdUpdateBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;
                                cmdUpdateBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                                cmdUpdateBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(row["DueDate"]);
                                cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();


                                cmdUpdateBilling.Transaction = txn;
                                cmdUpdateBilling.ExecuteNonQuery();
                                cmdUpdateBilling.Dispose();


                            }
                            else
                            {
                                string queryUpdateBilling = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo AND DueDate=@DueDate";

                                SqlCommand cmdUpdateBilling = new SqlCommand(queryUpdateBilling, conn);
                                cmdUpdateBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = tempPaymentValue;// row[recBilling.Fields.PaymentValue] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.PaymentValue].ToString());
                                cmdUpdateBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// Convert.ToDecimal(row["Value"]) - tempPaymentValue;
                                //cmdUpdateBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                                cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();
                                cmdUpdateBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(row["DueDate"]);

                                cmdUpdateBilling.Transaction = txn;
                                cmdUpdateBilling.ExecuteNonQuery();
                                cmdUpdateBilling.Dispose();
                            }

                            tempPaymentValue = tempPaymentValue - Convert.ToDecimal(row["Value"]);

                            if (tempPaymentValue == 0 || tempPaymentValue < 0)
                                break;
                        }
                        #endregion

                        #region  Delete Semua PrevBilling
                        foreach (DataRow row in dtDataBilling.Rows)
                        {
                            #region Ambil Data PrevBillingNo Berdasar BillingNo

                            string GetBillingNo = row["BillingNo"] == DBNull.Value ? string.Empty : row["BillingNo"].ToString();
                            string queryGetPrevBilling = " SELECT COUNT(*) FROM transactions.recBilling WHERE  PrevBillingNo = @BillingNo";
                            SqlCommand cmdGetPrevBilling = new SqlCommand(queryGetPrevBilling, conn);
                            cmdGetPrevBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = GetBillingNo;

                            cmdGetPrevBilling.Transaction = txn;
                            object jumlahData = cmdGetPrevBilling.ExecuteScalar();
                            int jumlahPrevBilling = jumlahData == null ? 0 : Convert.ToInt32(jumlahData);
                            cmdGetPrevBilling.Dispose();

                            #endregion

                            #region Delete Data recBilling Berdasar PrevBilling

                            if (jumlahPrevBilling > 0)
                            {
                                string queryDeletePrevBilling = "DELETE FROM transactions.recBilling WHERE  PrevBillingNo = @PrevBillingNo";
                                SqlCommand cmdDeletePrevBilling = new SqlCommand(queryDeletePrevBilling, conn);
                                cmdDeletePrevBilling.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = GetBillingNo;

                                cmdDeletePrevBilling.Transaction = txn;
                                cmdDeletePrevBilling.ExecuteNonQuery();
                                cmdDeletePrevBilling.Dispose();

                            }

                            #endregion
                        }
                        #endregion
                    }




                    #endregion

                    #region Update recSanksi recRegistration recActionCorporation

                    foreach (DataRow row in dtDataBilling.Rows)
                    {

                        string transTypeName = GetTransTypeName(Convert.ToInt16(row["TransTypeID"]));

                        if (transTypeName.Trim().ToLower().Contains("sanksi"))
                        {
                            string updateSanksi = @"UPDATE transactions.recSanction SET PaymentNo=@PaymentNo,PaymentDate=@PaymentDate WHERE InvoiceNo=@InvoiceNo";
                            SqlCommand cmdSanction = new SqlCommand(updateSanksi, conn);
                            cmdSanction.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                            cmdSanction.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                            cmdSanction.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                            cmdSanction.Transaction = txn;
                            cmdSanction.ExecuteNonQuery();
                            cmdSanction.Dispose();


                        }
                        else if (transTypeName.Trim().ToLower().Contains("registrasi"))
                        {
                            string updateRegistration = @"UPDATE transactions.recSectorRegistration SET PaymentNo=@PaymentNo,PaymentDate=@PaymentDate,PaymentValue=@PaymentValue,RegistrationStatusID=@RegistrationStatusID WHERE InvoiceNo=@InvoiceNo";
                            SqlCommand cmdRegistration = new SqlCommand(updateRegistration, conn);
                            cmdRegistration.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                            cmdRegistration.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                            cmdRegistration.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = domain.PaymentValue;
                            cmdRegistration.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                            cmdRegistration.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = 2;

                            cmdRegistration.Transaction = txn;
                            cmdRegistration.ExecuteNonQuery();
                            cmdRegistration.Dispose();
                        }
                        else if (transTypeName.Trim().ToLower().Contains("aksi korporasi"))
                        {
                            string updateAksi = @"UPDATE transactions.recCorporateAction SET PaymentNo=@PaymentNo,PaymentDate=@PaymentDate WHERE InvoiceNo=@InvoiceNo";
                            SqlCommand cmdAksi = new SqlCommand(updateAksi, conn);
                            cmdAksi.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                            cmdAksi.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                            cmdAksi.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                            cmdAksi.Transaction = txn;
                            cmdAksi.ExecuteNonQuery();
                            cmdAksi.Dispose();
                        }

                    }

                    #endregion


                    #region Lebih Bayar
                    if (InvoiceValue - domain.PaymentValue < 0)
                    {

                        #region Ambil TransTypeID dengan Kode 10

                        string strTransTypeID = @"SELECT TransTypeID FROM master.recTransactionType WHERE TransTypeCode=@TransTypeCode";


                        SqlCommand cmdTransTypeID = new SqlCommand(strTransTypeID, conn);
                        cmdTransTypeID.Parameters.Add("@TransTypeCode", SqlDbType.VarChar).Value = "10";
                        cmdTransTypeID.Transaction = txn;
                        var TransTypeID = cmdTransTypeID.ExecuteScalar();
                        int resultTransTypeID = TransTypeID == DBNull.Value ? 0 : int.Parse(TransTypeID.ToString());
                        cmdTransTypeID.Dispose();

                        #endregion

                        #region Generate BillingNo dari SP untuk lebih Bayar

                        SqlCommand cmdGenerateBillingLebih = conn.CreateCommand();
                        cmdGenerateBillingLebih.CommandText = "[dbo].[SP_GenTransCode]";
                        cmdGenerateBillingLebih.CommandType = CommandType.StoredProcedure;
                        cmdGenerateBillingLebih.Parameters.Add("@kode", SqlDbType.VarChar).Value = "10";
                        SqlParameter prmResult = new SqlParameter();
                        prmResult.ParameterName = "@hasil";
                        prmResult.Direction = ParameterDirection.Output;
                        prmResult.SqlDbType = SqlDbType.VarChar;
                        prmResult.Size = 100;
                        cmdGenerateBillingLebih.Parameters.Add(prmResult);
                        cmdGenerateBillingLebih.Transaction = txn;
                        cmdGenerateBillingLebih.ExecuteNonQuery();
                        cmdGenerateBillingLebih.Dispose();

                        var BillingNoLebihBayar = prmResult.Value;

                        #endregion

                        #region Get TermDay buat DebitNote dengan Kode 11

                        string queryDebitNote = " SELECT TermDay FROM master.recTransactionType  WHERE TransTypeCode=@TransTypeCode";

                        SqlCommand cmdDebitNote = new SqlCommand(queryDebitNote, conn);
                        cmdDebitNote.CommandType = CommandType.Text;
                        cmdDebitNote.Parameters.Add("@TransTypeCode", SqlDbType.Int).Value = 10;

                        cmdDebitNote.Transaction = txn;
                        object termDay = cmdDebitNote.ExecuteScalar();
                        cmdDebitNote.Dispose();

                        #endregion

                        #region Get DueDate Dari Function  Buat Debit Note

                        SqlCommand cmdSPDueDate = new SqlCommand("SELECT dbo.FN_CekDueDate(@Param1) DueDate", conn);
                        cmdSPDueDate.CommandType = CommandType.Text;
                        cmdSPDueDate.Parameters.Add("@Param1", SqlDbType.VarChar).Value = domain.PaymentDate.AddDays(Convert.ToInt32(termDay)).ToString("yyyyMMdd");
                        cmdSPDueDate.Transaction = txn;
                        object DuedateDebitNote = cmdSPDueDate.ExecuteScalar();
                        cmdSPDueDate.Dispose();


                        #endregion

                        string InvoiceNoLebihBayar = string.Empty;
                        int debtorsectorID = 0;

                        for (int i = 0; i < dtDataBilling.Rows.Count; i++)
                        {
                            InvoiceNoLebihBayar = dtDataBilling.Rows[i]["BillingNo"].ToString();
                            debtorsectorID = Convert.ToInt32(dtDataBilling.Rows[i]["DebtorSectorID"]);
                        }


                        #region Query Insert recBilling Lebih Bayar
                        string queryLebihBayar = @"INSERT INTO  transactions.recBilling (
                                                    TransTypeID,
                                                    BillingNo,
                                                    Value,
                                                    TariffID,
                                                    ForPayment,
                                                    PrevBillingNo,
                                                    DebtorSectorID,
                                                    BillingDate,
                                                    CreatedDate,
                                                    CreatedBy,
                                                    DueDate,
                                                    Balance
                                                    )
                                                    VALUES
                                                    (
                                                    @TransTypeID,
                                                    @BillingNo,
                                                    @Value,
                                                    NULL,
                                                    @ForPayment,
                                                    @PrevBillingNo,
                                                    @DebtorSectorID,
                                                    @BillingDate,
                                                    @CreatedDate,
                                                    @CreatedBy,
                                                    NULL,
                                                    @Balance
                                                    )
                                                    ";
                        #endregion

                        SqlCommand cmdLebihBayar = new SqlCommand(queryLebihBayar, conn);
                        cmdLebihBayar.CommandType = CommandType.Text;

                        cmdLebihBayar.Parameters.Add("@TransTypeID", SqlDbType.Int).Value = resultTransTypeID;
                        cmdLebihBayar.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = BillingNoLebihBayar.ToString().Trim();// //"10." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + resultBillingNo.ToString().PadLeft(5, '0');
                        cmdLebihBayar.Parameters.Add("@Value", SqlDbType.Money).Value = InvoiceValue - domain.PaymentValue;
                        cmdLebihBayar.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = "Lebih bayar dari No. Surat Setoran " + domain.InvoiceNo;
                        cmdLebihBayar.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = InvoiceNoLebihBayar;
                        cmdLebihBayar.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorsectorID;
                        cmdLebihBayar.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = DateTime.Now;
                        cmdLebihBayar.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                        cmdLebihBayar.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.UpdatedBy;
                        cmdLebihBayar.Parameters.Add("@Balance", SqlDbType.Decimal).Value = InvoiceValue - domain.PaymentValue;

                        //cmdLebihBayar.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(DuedateDebitNote);

                        cmdLebihBayar.Transaction = txn;
                        cmdLebihBayar.ExecuteNonQuery();
                        cmdLebihBayar.Dispose();

                    }
                    #endregion

                    #region Kurang Bayar


                    if (InvoiceValue - domain.PaymentValue > 0)
                    {

                        #region Ambil TransTypeID dengan Kode 11

                        string strTransTypeID2 = @"SELECT TransTypeID FROM master.recTransactionType WHERE TransTypeCode=@TransTypeCode";


                        SqlCommand cmdTransTypeID2 = new SqlCommand(strTransTypeID2, conn);
                        cmdTransTypeID2.Parameters.Add("@TransTypeCode", SqlDbType.VarChar).Value = "11";
                        cmdTransTypeID2.Transaction = txn;
                        var TransTypeID2 = cmdTransTypeID2.ExecuteScalar();
                        int resultTransTypeID2 = TransTypeID2 == DBNull.Value ? 0 : int.Parse(TransTypeID2.ToString());
                        cmdTransTypeID2.Dispose();

                        #endregion

                        #region Generate BillingNo dari SP untuk Kurang Bayar

                        SqlCommand cmdGenerateBillingKurang = conn.CreateCommand();
                        cmdGenerateBillingKurang.CommandText = "[dbo].[SP_GenTransCode]";
                        cmdGenerateBillingKurang.CommandType = CommandType.StoredProcedure;
                        cmdGenerateBillingKurang.Parameters.Add("@kode", SqlDbType.VarChar).Value = "11";
                        SqlParameter prmResultKurang = new SqlParameter();
                        prmResultKurang.ParameterName = "@hasil";
                        prmResultKurang.Direction = ParameterDirection.Output;
                        prmResultKurang.SqlDbType = SqlDbType.VarChar;
                        prmResultKurang.Size = 100;
                        cmdGenerateBillingKurang.Parameters.Add(prmResultKurang);
                        cmdGenerateBillingKurang.Transaction = txn;
                        cmdGenerateBillingKurang.ExecuteNonQuery();
                        cmdGenerateBillingKurang.Dispose();

                        var BillingNoKurangBayar = prmResultKurang.Value;

                        #endregion

                        #region Get TermDay buat CreditNote dengan Kode 11

                        string queryCreditNote = " SELECT TermDay FROM master.recTransactionType  WHERE TransTypeCode=@TransTypeCode";

                        SqlCommand cmdCreditNote = new SqlCommand(queryCreditNote, conn);
                        cmdCreditNote.CommandType = CommandType.Text;
                        cmdCreditNote.Parameters.Add("@TransTypeCode", SqlDbType.Int).Value = 11;

                        cmdCreditNote.Transaction = txn;
                        object termDay = cmdCreditNote.ExecuteScalar();
                        cmdCreditNote.Dispose();

                        #endregion

                        #region Get DueDate Dari Function  Buat Credit Note

                        SqlCommand cmdSPDueDate = new SqlCommand("SELECT dbo.FN_CekDueDate(@Param1) DueDate", conn);
                        cmdSPDueDate.CommandType = CommandType.Text;
                        cmdSPDueDate.Parameters.Add("@Param1", SqlDbType.VarChar).Value = domain.PaymentDate.AddDays(Convert.ToInt32(termDay)).ToString("yyyyMMdd");
                        cmdSPDueDate.Transaction = txn;
                        object DuedateCreditNote = cmdSPDueDate.ExecuteScalar();
                        cmdSPDueDate.Dispose();


                        #endregion

                        decimal tempValue = domain.PaymentValue;
                        string InvoiceNoKurangBayar = string.Empty;
                        int debtorsectorID = 0;
                        foreach (DataRow row in dtDataBilling.Rows)
                        {
                            tempValue = tempValue - Convert.ToDecimal(row["Value"]);
                            InvoiceNoKurangBayar = row["BillingNo"].ToString();
                            debtorsectorID = Convert.ToInt32(row["DebtorSectorID"]);
                            if (tempValue == 0 || tempValue < 0)
                                break;
                        }

                        #region Query Insert recBilling Lebih Bayar
                        string queryKurangBayar = @"INSERT INTO  transactions.recBilling (
                                                    TransTypeID,
                                                    BillingNo,
                                                    Value,
                                                    TariffID,
                                                    ForPayment,
                                                    PrevBillingNo,
                                                    DebtorSectorID,
                                                    BillingDate,
                                                    CreatedDate,
                                                    CreatedBy,
                                                    DueDate,
                                                    Balance
                                                    )
                                                    VALUES
                                                    (
                                                    @TransTypeID,
                                                    @BillingNo,
                                                    @Value,
                                                    NULL,
                                                    @ForPayment,
                                                    @PrevBillingNo,
                                                    @DebtorSectorID,
                                                    @BillingDate,
                                                    @CreatedDate,
                                                    @CreatedBy,
                                                    @DueDate,
                                                    @Balance
                                                    )
                                                    ";
                        #endregion

                        SqlCommand cmdKurangBayar = new SqlCommand(queryKurangBayar, conn);
                        cmdKurangBayar.CommandType = CommandType.Text;

                        cmdKurangBayar.Parameters.Add("@TransTypeID", SqlDbType.Int).Value = resultTransTypeID2;
                        cmdKurangBayar.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = BillingNoKurangBayar.ToString().Trim();// "11." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + resultBillingNo2.ToString().PadLeft(5, '0');
                        cmdKurangBayar.Parameters.Add("@Value", SqlDbType.Money).Value = InvoiceValue - domain.PaymentValue;
                        cmdKurangBayar.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = "Kurang bayar dari No. Surat Setoran " + domain.InvoiceNo;
                        cmdKurangBayar.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = InvoiceNoKurangBayar;
                        cmdKurangBayar.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorsectorID;
                        cmdKurangBayar.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = DateTime.Now;
                        cmdKurangBayar.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                        cmdKurangBayar.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.UpdatedBy;
                        cmdKurangBayar.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(DuedateCreditNote);
                        cmdKurangBayar.Parameters.Add("@Balance", SqlDbType.Decimal).Value = InvoiceValue - domain.PaymentValue;

                        cmdKurangBayar.Transaction = txn;
                        cmdKurangBayar.ExecuteNonQuery();
                        cmdKurangBayar.Dispose();
                    }
                    #endregion

                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

                //}
            }
            return result;
        }

        public bool DeletePembayaran(List<recPayment> listPayment)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    foreach (recPayment domain in listPayment)
                    {

                        #region Delete Data Payment
                        string queryPayment = @" DELETE FROM transactions.recPayment 
                                                WHERE PaymentID=@PaymentID 
                                               ";

                        SqlCommand cmd = new SqlCommand(queryPayment, conn);
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("@PaymentID", SqlDbType.VarChar).Value = domain.PaymentID;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();

                        #endregion

                        #region Update Data recInvoice

                        #region Ambil InvoiceValue berdasar InvoiceNo

                        string strInvoiceValue = @"SELECT InvoiceValue  FROM transactions.recInvoice WHERE InvoiceNo=@InvoiceNo";
                        SqlCommand cmdInvoiceValue = new SqlCommand(strInvoiceValue, conn);
                        cmdInvoiceValue.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                        cmdInvoiceValue.Transaction = txn;
                        decimal InvoiceValue = Convert.ToDecimal(cmdInvoiceValue.ExecuteScalar());
                        cmdInvoiceValue.Dispose();

                        #endregion

                        #region Query Update recInvoice

                        string queryInvoice = @"UPDATE  transactions.recInvoice SET PaymentNo=NULL,
                                            PaymentDate=NULL,
                                            PaymentValue=@PaymentValue,
                                            Balance=@Balance
                                            WHERE InvoiceNo=@InvoiceNo";

                        SqlCommand cmdInvoice = new SqlCommand(queryInvoice, conn);
                        cmdInvoice.CommandType = CommandType.Text;


                        cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = 0;
                        cmdInvoice.Parameters.Add("@Balance", SqlDbType.Decimal).Value = domain.PaymentValue;
                        cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                        cmdInvoice.Transaction = txn;
                        cmdInvoice.ExecuteNonQuery();
                        cmdInvoice.Dispose();

                        #endregion


                        #endregion

                        #region UPDATE recBilling

                        //DataTable dt = GetDataRecBilling(domain.InvoiceNo);

                        #region Ambil Data Billing Berdasar InvoiceNo

                        string getdataRecBilling = @"SELECT BillingNo
                                                    ,DebtorSectorID
                                                    ,TransTypeID
                                                    ,TariffID
                                                    ,Value
                                                    ,InvoiceNo
                                                    ,InvoiceDetailID
                                                    ,Balance
                                                    ,PrevBillingNo
                                                    ,BillingDate
                                                    ,ISNULL(DueDate,'01/01/1900') DueDate
                                                    ,IsCancel
                                                    ,ForPayment
                                                    ,SaikoNo
                                                    ,CreatedDate
                                                    ,CreatedBy
                                                    ,UpdatedDate
                                                    ,UpdatedBy
                                                    ,CalculationID
                                                    ,PaymentValue
                                                    ,IsManual
                                                    ,IsHold
                                                    ,IsPosting
                                                    ,ObjectionNo
                                                FROM transactions.recBilling WHERE InvoiceNo=@InvoiceNo";
                        SqlCommand cmdDataBilling = new SqlCommand(getdataRecBilling, conn);
                        cmdDataBilling.CommandType = CommandType.Text;
                        cmdDataBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                        cmdDataBilling.Transaction = txn;
                        SqlDataAdapter da = new SqlDataAdapter();
                        DataTable dtDataBilling = new DataTable();
                        da.SelectCommand = cmdDataBilling;
                        da.Fill(dtDataBilling);
                        cmdDataBilling.Dispose();

                        #endregion

                        #region Update recBilling dengan PaymentValue = 0

                        foreach (DataRow row in dtDataBilling.Rows)
                        {

                            string queryUpdateBillingPayment = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo";

                            SqlCommand cmdUpdateBillingPayment = new SqlCommand(queryUpdateBillingPayment, conn);
                            cmdUpdateBillingPayment.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = 0;
                            decimal value = row[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.Value].ToString());
                            cmdUpdateBillingPayment.Parameters.Add("@Balance", SqlDbType.Decimal).Value = value;
                            cmdUpdateBillingPayment.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();

                            cmdUpdateBillingPayment.Transaction = txn;
                            cmdUpdateBillingPayment.ExecuteNonQuery();
                            cmdUpdateBillingPayment.Dispose();

                        }

                        #endregion

                        #region  Delete Semua PrevBilling
                        foreach (DataRow row in dtDataBilling.Rows)
                        {
                            #region Ambil Data PrevBillingNo Berdasar BillingNo

                            string BillingNo = row["BillingNo"] == DBNull.Value ? string.Empty : row["BillingNo"].ToString();
                            string queryGetPrevBilling = " SELECT COUNT(*) FROM transactions.recBilling WHERE  PrevBillingNo = @BillingNo";
                            SqlCommand cmdGetPrevBilling = new SqlCommand(queryGetPrevBilling, conn);
                            cmdGetPrevBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = BillingNo;

                            cmdGetPrevBilling.Transaction = txn;
                            object jumlahData = cmdGetPrevBilling.ExecuteScalar();
                            int jumlahPrevBilling = jumlahData == null ? 0 : Convert.ToInt32(jumlahData);
                            cmdGetPrevBilling.Dispose();

                            #endregion

                            #region Delete Data recBilling Berdasar PrevBilling

                            if (jumlahPrevBilling > 0)
                            {
                                string queryDeletePrevBilling = "DELETE FROM transactions.recBilling WHERE  PrevBillingNo = @PrevBillingNo";
                                SqlCommand cmdDeletePrevBilling = new SqlCommand(queryDeletePrevBilling, conn);
                                cmdDeletePrevBilling.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = BillingNo;

                                cmdDeletePrevBilling.Transaction = txn;
                                cmdDeletePrevBilling.ExecuteNonQuery();
                                cmdDeletePrevBilling.Dispose();

                            }

                            #endregion
                        }
                        #endregion


                        #endregion

                        #region Update recSanksi recRegistration recActionCorporation

                        foreach (DataRow row in dtDataBilling.Rows)
                        {

                            string transTypeName = GetTransTypeName(Convert.ToInt16(row["TransTypeID"]));

                            if (transTypeName.Trim().ToLower().Contains("sanksi"))
                            {
                                string updateSanksi = @"UPDATE transactions.recSanction SET PaymentNo=NULL,PaymentDate=NULL WHERE InvoiceNo=@InvoiceNo";
                                SqlCommand cmdSanction = new SqlCommand(updateSanksi, conn);
                                //cmdSanction.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                                //cmdSanction.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                                cmdSanction.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                                cmdSanction.Transaction = txn;
                                cmdSanction.ExecuteNonQuery();
                                cmdSanction.Dispose();


                            }
                            else if (transTypeName.Trim().ToLower().Contains("registrasi"))
                            {
                                string updateRegistration = @"UPDATE transactions.recSectorRegistration SET PaymentNo=NULL,PaymentDate=NULL,PaymentValue=NULL WHERE InvoiceNo=@InvoiceNo";
                                SqlCommand cmdRegistration = new SqlCommand(updateRegistration, conn);
                                //cmdRegistration.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                                //cmdRegistration.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                                //cmdRegistration.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = domain.PaymentValue;
                                cmdRegistration.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                                //cmdRegistration.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = 2;

                                cmdRegistration.Transaction = txn;
                                cmdRegistration.ExecuteNonQuery();
                                cmdRegistration.Dispose();
                            }
                            else if (transTypeName.Trim().ToLower().Contains("aksi korporasi"))
                            {
                                string updateAksi = @"UPDATE transactions.recCorporateAction SET PaymentNo=NULL,PaymentDate=NULL WHERE InvoiceNo=@InvoiceNo";
                                SqlCommand cmdAksi = new SqlCommand(updateAksi, conn);
                                //cmdAksi.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = domain.PaymentNo;
                                //cmdAksi.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                                cmdAksi.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                                cmdAksi.Transaction = txn;
                                cmdAksi.ExecuteNonQuery();
                                cmdAksi.Dispose();
                            }

                        }

                        #endregion

                        
                    }
                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

                //}
            }
            return result;
        }

        public bool UpdatePembayaran(List<recPayment> listPayment)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {

                    foreach (recPayment domain in listPayment)
                    {
                        #region Generate PaymentNo dari Stored Procedure

                        SqlCommand cmdGeneratePaymentNo = conn.CreateCommand();
                        cmdGeneratePaymentNo.CommandText = "[dbo].[SP_GenTransCode]";
                        cmdGeneratePaymentNo.CommandType = CommandType.StoredProcedure;
                        cmdGeneratePaymentNo.Parameters.Add("@kode", SqlDbType.VarChar).Value = "8";
                        SqlParameter prmResultPaymentNo = new SqlParameter();
                        prmResultPaymentNo.ParameterName = "@hasil";
                        prmResultPaymentNo.Direction = ParameterDirection.Output;
                        prmResultPaymentNo.SqlDbType = SqlDbType.VarChar;
                        prmResultPaymentNo.Size = 100;
                        cmdGeneratePaymentNo.Parameters.Add(prmResultPaymentNo);
                        cmdGeneratePaymentNo.Transaction = txn;
                        cmdGeneratePaymentNo.ExecuteNonQuery();
                        cmdGeneratePaymentNo.Dispose();

                        var GenPaymentNo = prmResultPaymentNo.Value;

                        string PaymentNo = GenPaymentNo.ToString().Trim();
                        //domain.PaymentNo = GenPaymentNo.ToString().Trim();

                        #endregion


                        #region Query Insert Ke recPayment
                        string queryPayment = @"INSERT INTO transactions.recPayment(
                                                TransDate,
                                                PaymentNo,
                                                PaymentDate,
                                                PaymentValue,
                                                InvoiceNo,
                                                DebtorSectorID,
                                                PostingDate,
                                                PostingBy,
                                                CreatedDate,
                                                CreatedBy,
                                                UpdatedDate,
                                                UpdatedBy,
                                                Remark
                                                ) 
                                                VALUES(
                                                @TransDate,
                                                @PaymentNo,
                                                @PaymentDate,
                                                @PaymentValue,
                                                @InvoiceNo,
                                                @DebtorSectorID,
                                                @PostingDate,
                                                @PostingBy,
                                                @CreatedDate,
                                                @CreatedBy,
                                                @UpdatedDate,
                                                @UpdatedBy,
                                                @Remark
                                                )";

                        SqlCommand cmd = new SqlCommand(queryPayment, conn);
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("@TransDate", SqlDbType.Date).Value = domain.TransDate;
                        cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = PaymentNo;
                        cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                        cmd.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = domain.PaymentValue;
                        cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                        cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)domain.DebtorSectorID ?? DBNull.Value;
                        cmd.Parameters.Add("@PostingDate", SqlDbType.VarChar).Value = (object)domain.PostingDate ?? DBNull.Value;
                        cmd.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)domain.PostingBy ?? DBNull.Value;
                        cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                        cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                        cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                        cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                        cmd.Parameters.Add("@Remark", SqlDbType.VarChar).Value = (object)domain.Remark ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        #endregion

                        #region Update Data recInvoice

                        #region Ambil InvoiceValue berdasar InvoiceNo

                        string strInvoiceValue = @"SELECT InvoiceValue  FROM transactions.recInvoice WHERE InvoiceNo=@InvoiceNo";
                        SqlCommand cmdInvoiceValue = new SqlCommand(strInvoiceValue, conn);
                        cmdInvoiceValue.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                        cmdInvoiceValue.Transaction = txn;
                        decimal InvoiceValue = Convert.ToDecimal(cmdInvoiceValue.ExecuteScalar());
                        cmdInvoiceValue.Dispose();

                        #endregion

                        #region Query Update recInvoice

                        string queryInvoice = @"UPDATE  transactions.recInvoice SET PaymentNo=@PaymentNo,
                                            PaymentDate=@PaymentDate,
                                            PaymentValue=@PaymentValue,
                                            Balance=@Balance
                                            WHERE InvoiceNo=@InvoiceNo";

                        SqlCommand cmdInvoice = new SqlCommand(queryInvoice, conn);
                        cmdInvoice.CommandType = CommandType.Text;

                        cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = PaymentNo;
                        cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = domain.PaymentDate;
                        cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = domain.PaymentValue;
                        cmdInvoice.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// InvoiceValue - domain.PaymentValue;
                        cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                        cmdInvoice.Transaction = txn;
                        cmdInvoice.ExecuteNonQuery();
                        cmdInvoice.Dispose();

                        #endregion


                        #endregion

                        #region UPDATE recBilling

                        //DataTable dt = GetDataRecBilling(domain.InvoiceNo);

                        #region Ambil Data Billing Berdasar InvoiceNo

                        string getdataRecBilling = @"SELECT BillingNo
                                                    ,DebtorSectorID
                                                    ,TransTypeID
                                                    ,TariffID
                                                    ,Value
                                                    ,InvoiceNo
                                                    ,InvoiceDetailID
                                                    ,Balance
                                                    ,PrevBillingNo
                                                    ,BillingDate
                                                    ,ISNULL(DueDate,'01/01/1900') DueDate
                                                    ,IsCancel
                                                    ,ForPayment
                                                    ,SaikoNo
                                                    ,CreatedDate
                                                    ,CreatedBy
                                                    ,UpdatedDate
                                                    ,UpdatedBy
                                                    ,CalculationID
                                                    ,PaymentValue
                                                    ,IsManual
                                                    ,IsHold
                                                    ,IsPosting
                                                    ,ObjectionNo
                                                FROM transactions.recBilling WHERE InvoiceNo=@InvoiceNo";
                        
                        SqlCommand cmdDataBilling = new SqlCommand(getdataRecBilling, conn);
                        cmdDataBilling.CommandType = CommandType.Text;
                        cmdDataBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                        cmdDataBilling.Transaction = txn;
                        SqlDataAdapter da = new SqlDataAdapter();
                        DataTable dtDataBilling = new DataTable();
                        da.SelectCommand = cmdDataBilling;
                        da.Fill(dtDataBilling);
                        cmdDataBilling.Dispose();

                        #endregion

                        #region Update Semua Balance recBilling jadi 0
                        foreach (DataRow row in dtDataBilling.Rows)
                        {
                            string updateBalanceRecBilling = "UPDATE transactions.recBilling SET Balance = @Balance WHERE BillingNo = @BillingNo";

                            SqlCommand cmdUpdateBalanceBilling = new SqlCommand(updateBalanceRecBilling, conn);
                            cmdUpdateBalanceBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();
                            cmdUpdateBalanceBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;


                            cmdUpdateBalanceBilling.Transaction = txn;
                            cmdUpdateBalanceBilling.ExecuteNonQuery();
                            cmdUpdateBalanceBilling.Dispose();
                        }
                        #endregion

                        if (dtDataBilling.Rows.Count == 1)
                        {
                            #region Jika Data Billing Jumlahnya 1
                            foreach (DataRow row in dtDataBilling.Rows)
                            {
                                string queryUpdateBilling = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo";

                                SqlCommand cmdUpdateBilling = new SqlCommand(queryUpdateBilling, conn);
                                cmdUpdateBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = domain.PaymentValue;// == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.PaymentValue].ToString());
                                decimal value = row[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.Value].ToString());
                                cmdUpdateBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// value - domain.PaymentValue;
                                cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();

                                cmdUpdateBilling.Transaction = txn;
                                cmdUpdateBilling.ExecuteNonQuery();
                                cmdUpdateBilling.Dispose();

                            }

                            #endregion

                        }

                        else if (dtDataBilling.Rows.Count > 1)
                        {
                            #region Jika Jumlah Data Billing Lebih Dari 1
                            decimal tempPaymentValue = domain.PaymentValue;
                            foreach (DataRow row in dtDataBilling.Rows)
                            {
                                if (tempPaymentValue >= Convert.ToDecimal(row["Value"]))
                                {
                                    string queryUpdateBilling = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo AND DueDate=@DueDate";

                                    SqlCommand cmdUpdateBilling = new SqlCommand(queryUpdateBilling, conn);
                                    cmdUpdateBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = Convert.ToDecimal(row["Value"]);// row[recBilling.Fields.PaymentValue] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.PaymentValue].ToString());
                                    cmdUpdateBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;
                                    //cmdUpdateBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                                    cmdUpdateBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(row["DueDate"]);
                                    cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();

                                    cmdUpdateBilling.Transaction = txn;
                                    cmdUpdateBilling.ExecuteNonQuery();
                                    cmdUpdateBilling.Dispose();


                                }
                                else
                                {
                                    string queryUpdateBilling = @"UPDATE transactions.recBilling SET PaymentValue =@PaymentValue,
                                                                 Balance = @Balance 
                                                                 WHERE BillingNo = @BillingNo AND DueDate=@DueDate";

                                    SqlCommand cmdUpdateBilling = new SqlCommand(queryUpdateBilling, conn);
                                    cmdUpdateBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = tempPaymentValue;// row[recBilling.Fields.PaymentValue] == DBNull.Value ? 0 : decimal.Parse(row[recBilling.Fields.PaymentValue].ToString());
                                    cmdUpdateBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = 0;// Convert.ToDecimal(row["Value"]) - tempPaymentValue;
                                    //cmdUpdateBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                                    cmdUpdateBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(row["DueDate"]);
                                    cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = row["BillingNo"].ToString().Trim();

                                    cmdUpdateBilling.Transaction = txn;
                                    cmdUpdateBilling.ExecuteNonQuery();
                                    cmdUpdateBilling.Dispose();
                                }

                                tempPaymentValue = tempPaymentValue - Convert.ToDecimal(row["Value"]);

                                if (tempPaymentValue == 0 || tempPaymentValue < 0)
                                    break;
                            }
                            #endregion
                        }




                        #endregion

                        #region Update recSanksi recRegistration recActionCorporation

                        foreach (DataRow row in dtDataBilling.Rows)
                        {

                            string transTypeName = GetTransTypeName(Convert.ToInt16(row["TransTypeID"]));

                            if (transTypeName.Trim().ToLower().Contains("sanksi"))
                            {
                                string updateSanksi = @"UPDATE transactions.recSanction SET PaymentNo=@PaymentNo,PaymentDate=@PaymentDate WHERE InvoiceNo=@InvoiceNo";
                                SqlCommand cmdSanction = new SqlCommand(updateSanksi, conn);
                                cmdSanction.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = PaymentNo;
                                cmdSanction.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = DateTime.Now;
                                cmdSanction.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                                cmdSanction.Transaction = txn;
                                cmdSanction.ExecuteNonQuery();
                                cmdSanction.Dispose();


                            }
                            else if (transTypeName.Trim().ToLower().Contains("registrasi"))
                            {
                                string updateRegistration = @"UPDATE transactions.recSectorRegistration SET PaymentNo=@PaymentNo,PaymentDate=@PaymentDate,PaymentValue=@PaymentValue,RegistrationStatusID=@RegistrationStatusID WHERE InvoiceNo=@InvoiceNo";
                                SqlCommand cmdRegistration = new SqlCommand(updateRegistration, conn);
                                cmdRegistration.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = PaymentNo;
                                cmdRegistration.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = DateTime.Now;
                                cmdRegistration.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = domain.PaymentValue;
                                cmdRegistration.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                                cmdRegistration.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = 2;

                                cmdRegistration.Transaction = txn;
                                cmdRegistration.ExecuteNonQuery();
                                cmdRegistration.Dispose();
                            }
                            else if (transTypeName.Trim().ToLower().Contains("aksi korporasi"))
                            {
                                string updateAksi = @"UPDATE transactions.recCorporateAction SET PaymentNo=@PaymentNo,PaymentDate=@PaymentDate WHERE InvoiceNo=@InvoiceNo";
                                SqlCommand cmdAksi = new SqlCommand(updateAksi, conn);
                                cmdAksi.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = PaymentNo;
                                cmdAksi.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = DateTime.Now;
                                cmdAksi.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;

                                cmdAksi.Transaction = txn;
                                cmdAksi.ExecuteNonQuery();
                                cmdAksi.Dispose();
                            }

                        }

                        #endregion



                        #region Lebih Bayar
                        if (InvoiceValue - domain.PaymentValue < 0)
                        {
                            #region Ambil TransTypeID dengan Kode 10

                            string strTransTypeID = @"SELECT TransTypeID FROM master.recTransactionType WHERE TransTypeCode=@TransTypeCode";


                            SqlCommand cmdTransTypeID = new SqlCommand(strTransTypeID, conn);
                            cmdTransTypeID.Parameters.Add("@TransTypeCode", SqlDbType.VarChar).Value = "10";
                            cmdTransTypeID.Transaction = txn;
                            var TransTypeID = cmdTransTypeID.ExecuteScalar();
                            int resultTransTypeID = TransTypeID == DBNull.Value ? 0 : int.Parse(TransTypeID.ToString());
                            cmdTransTypeID.Dispose();

                            #endregion

                            #region Generate BillingNo dari SP untuk lebih Bayar

                            SqlCommand cmdGenerateBillingLebih = conn.CreateCommand();
                            cmdGenerateBillingLebih.CommandText = "[dbo].[SP_GenTransCode]";
                            cmdGenerateBillingLebih.CommandType = CommandType.StoredProcedure;
                            cmdGenerateBillingLebih.Parameters.Add("@kode", SqlDbType.VarChar).Value = "10";
                            SqlParameter prmResult = new SqlParameter();
                            prmResult.ParameterName = "@hasil";
                            prmResult.Direction = ParameterDirection.Output;
                            prmResult.SqlDbType = SqlDbType.VarChar;
                            prmResult.Size = 100;
                            cmdGenerateBillingLebih.Parameters.Add(prmResult);
                            cmdGenerateBillingLebih.Transaction = txn;
                            cmdGenerateBillingLebih.ExecuteNonQuery();
                            cmdGenerateBillingLebih.Dispose();

                            var BillingNoLebihBayar = prmResult.Value;

                            #endregion

                            #region Get TermDay buat DebitNote dengan Kode 11

                            string queryDebitNote = " SELECT TermDay FROM master.recTransactionType  WHERE TransTypeCode=@TransTypeCode";

                            SqlCommand cmdDebitNote = new SqlCommand(queryDebitNote, conn);
                            cmdDebitNote.CommandType = CommandType.Text;
                            cmdDebitNote.Parameters.Add("@TransTypeCode", SqlDbType.Int).Value = 11;

                            cmdDebitNote.Transaction = txn;
                            object termDay = cmdDebitNote.ExecuteScalar();
                            cmdDebitNote.Dispose();

                            #endregion

                            #region Get DueDate Dari Function  Buat Debit Note

                            SqlCommand cmdSPDueDate = new SqlCommand("SELECT dbo.FN_CekDueDate(@Param1) DueDate", conn);
                            cmdSPDueDate.CommandType = CommandType.Text;
                            cmdSPDueDate.Parameters.Add("@Param1", SqlDbType.VarChar).Value = domain.PaymentDate.AddDays(Convert.ToInt32(termDay)).ToString("yyyyMMdd");
                            cmdSPDueDate.Transaction = txn;
                            object DuedateDebitNote = cmdSPDueDate.ExecuteScalar();
                            cmdSPDueDate.Dispose();


                            #endregion

                            string InvoiceNoLebihBayar = string.Empty;
                            int debtorsectorID = 0;

                            for (int i = 0; i < dtDataBilling.Rows.Count; i++)
                            {
                                InvoiceNoLebihBayar = dtDataBilling.Rows[i]["BillingNo"].ToString();
                                debtorsectorID = Convert.ToInt32(dtDataBilling.Rows[i]["DebtorSectorID"]);
                            }


                            #region Query Insert recBilling Lebih Bayar
                            string queryLebihBayar = @"INSERT INTO  transactions.recBilling (
                                                    TransTypeID,
                                                    BillingNo,
                                                    Value,
                                                    TariffID,
                                                    ForPayment,
                                                    PrevBillingNo,
                                                    DebtorSectorID,
                                                    BillingDate,
                                                    CreatedDate,
                                                    CreatedBy,
                                                    DueDate,
                                                    Balance
                                                    )
                                                    VALUES
                                                    (
                                                    @TransTypeID,
                                                    @BillingNo,
                                                    @Value,
                                                    NULL,
                                                    @ForPayment,
                                                    @PrevBillingNo,
                                                    @DebtorSectorID,
                                                    @BillingDate,
                                                    @CreatedDate,
                                                    @CreatedBy,
                                                    NULL,
                                                    Balance
                                                   )
                                                    ";
                            #endregion

                            SqlCommand cmdLebihBayar = new SqlCommand(queryLebihBayar, conn);
                            cmdLebihBayar.CommandType = CommandType.Text;

                            cmdLebihBayar.Parameters.Add("@TransTypeID", SqlDbType.Int).Value = resultTransTypeID;
                            cmdLebihBayar.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = BillingNoLebihBayar.ToString().Trim();// "10." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + resultBillingNo.ToString().PadLeft(5, '0');
                            cmdLebihBayar.Parameters.Add("@Value", SqlDbType.Money).Value = InvoiceValue - domain.PaymentValue;
                            cmdLebihBayar.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = "Lebih bayar dari No. Surat Setoran " + domain.InvoiceNo;
                            cmdLebihBayar.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = InvoiceNoLebihBayar;
                            cmdLebihBayar.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorsectorID;
                            cmdLebihBayar.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = DateTime.Now;
                            cmdLebihBayar.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                            cmdLebihBayar.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                            cmdLebihBayar.Parameters.Add("@Balance", SqlDbType.Decimal).Value = InvoiceValue - domain.PaymentValue;

                            cmdLebihBayar.Transaction = txn;
                            cmdLebihBayar.ExecuteNonQuery();
                            cmdLebihBayar.Dispose();

                        }
                        #endregion

                        #region Kurang Bayar


                        if (InvoiceValue - domain.PaymentValue > 0)
                        {
                            #region Ambil TransTypeID dengan Kode 11

                            string strTransTypeID2 = @"SELECT TransTypeID FROM master.recTransactionType WHERE TransTypeCode=@TransTypeCode";


                            SqlCommand cmdTransTypeID2 = new SqlCommand(strTransTypeID2, conn);
                            cmdTransTypeID2.Parameters.Add("@TransTypeCode", SqlDbType.VarChar).Value = "11";
                            cmdTransTypeID2.Transaction = txn;
                            var TransTypeID2 = cmdTransTypeID2.ExecuteScalar();
                            int resultTransTypeID2 = TransTypeID2 == DBNull.Value ? 0 : int.Parse(TransTypeID2.ToString());
                            cmdTransTypeID2.Dispose();

                            #endregion

                            #region Generate BillingNo dari SP untuk Kurang Bayar

                            SqlCommand cmdGenerateBillingKurang = conn.CreateCommand();
                            cmdGenerateBillingKurang.CommandText = "[dbo].[SP_GenTransCode]";
                            cmdGenerateBillingKurang.CommandType = CommandType.StoredProcedure;
                            cmdGenerateBillingKurang.Parameters.Add("@kode", SqlDbType.VarChar).Value = "11";
                            SqlParameter prmResultKurang = new SqlParameter();
                            prmResultKurang.ParameterName = "@hasil";
                            prmResultKurang.Direction = ParameterDirection.Output;
                            prmResultKurang.SqlDbType = SqlDbType.VarChar;
                            prmResultKurang.Size = 100;
                            cmdGenerateBillingKurang.Parameters.Add(prmResultKurang);
                            cmdGenerateBillingKurang.Transaction = txn;
                            cmdGenerateBillingKurang.ExecuteNonQuery();
                            cmdGenerateBillingKurang.Dispose();

                            var BillingNoKurangBayar = prmResultKurang.Value;

                            #endregion


                            #region Get TermDay buat CreditNote dengan Kode 10

                            string queryCreditNote = " SELECT TermDay FROM master.recTransactionType  WHERE TransTypeCode=@TransTypeCode";

                            SqlCommand cmdCreditNote = new SqlCommand(queryCreditNote, conn);
                            cmdCreditNote.CommandType = CommandType.Text;
                            cmdCreditNote.Parameters.Add("@TransTypeCode", SqlDbType.Int).Value = 10;

                            cmdCreditNote.Transaction = txn;
                            object termDay = cmdCreditNote.ExecuteScalar();
                            cmdCreditNote.Dispose();

                            #endregion

                            #region Get DueDate Dari Function  Buat Credit Note

                            SqlCommand cmdSPDueDate = new SqlCommand("SELECT dbo.FN_CekDueDate(@Param1) DueDate", conn);
                            cmdSPDueDate.CommandType = CommandType.Text;
                            cmdSPDueDate.Parameters.Add("@Param1", SqlDbType.VarChar).Value = domain.PaymentDate.AddDays(Convert.ToInt32(termDay)).ToString("yyyyMMdd");
                            cmdSPDueDate.Transaction = txn;
                            object DuedateCreditNote = cmdSPDueDate.ExecuteScalar();
                            cmdSPDueDate.Dispose();


                            #endregion



                            decimal tempValue = domain.PaymentValue;
                            string InvoiceNoKurangBayar = string.Empty;
                            int debtorsectorID = 0;
                            foreach (DataRow row in dtDataBilling.Rows)
                            {
                                tempValue = tempValue - Convert.ToDecimal(row["Value"]);
                                InvoiceNoKurangBayar = row["BillingNo"].ToString();
                                debtorsectorID = Convert.ToInt32(row["DebtorSectorID"]);
                                if (tempValue == 0 || tempValue < 0)
                                    break;
                            }

                            #region Query Insert recBilling Lebih Bayar
                            string queryKurangBayar = @"INSERT INTO  transactions.recBilling (
                                                    TransTypeID,
                                                    BillingNo,
                                                    Value,
                                                    TariffID,
                                                    ForPayment,
                                                    PrevBillingNo,
                                                    DebtorSectorID,
                                                    BillingDate,
                                                    CreatedDate,
                                                    CreatedBy,
                                                    DueDate,
                                                    Balance
                                                   )
                                                    VALUES
                                                    (
                                                    @TransTypeID,
                                                    @BillingNo,
                                                    @Value,
                                                    NULL,
                                                    @ForPayment,
                                                    @PrevBillingNo,
                                                    @DebtorSectorID,
                                                    @BillingDate,
                                                    @CreatedDate,
                                                    @CreatedBy,
                                                    @DueDate,
                                                    @Balance
                                                    )
                                                    ";
                            #endregion

                            SqlCommand cmdKurangBayar = new SqlCommand(queryKurangBayar, conn);
                            cmdKurangBayar.CommandType = CommandType.Text;

                            cmdKurangBayar.Parameters.Add("@TransTypeID", SqlDbType.Int).Value = resultTransTypeID2;
                            cmdKurangBayar.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = BillingNoKurangBayar.ToString().Trim();////"11." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + resultBillingNo2.ToString().PadLeft(5, '0');
                            cmdKurangBayar.Parameters.Add("@Value", SqlDbType.Money).Value = InvoiceValue - domain.PaymentValue;
                            cmdKurangBayar.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = "Kurang bayar dari No. Surat Setoran " + domain.InvoiceNo;
                            cmdKurangBayar.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = InvoiceNoKurangBayar;
                            cmdKurangBayar.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorsectorID;
                            cmdKurangBayar.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = DateTime.Now;
                            cmdKurangBayar.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                            cmdKurangBayar.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                            cmdKurangBayar.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(DuedateCreditNote);
                            cmdKurangBayar.Parameters.Add("@Balance", SqlDbType.Decimal).Value = InvoiceValue - domain.PaymentValue;


                            cmdKurangBayar.Transaction = txn;
                            cmdKurangBayar.ExecuteNonQuery();
                            cmdKurangBayar.Dispose();
                        }
                        #endregion
                  }
                    result = true;
                    txn.Commit();
               }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }


        /// <summary>
        /// Return list(int) that's indicate  a set of list<int> sucess update within this function
        /// </summary>
        /// <param name="recpaymentID"></param>
        /// <returns></returns>
        public List<string> UpdateUnverifiedPayment(List<string> invoiceNo)
        {
            List<string> resultSucess = new List<string>();
            #region Select Dulu (remark)
            //            recPayment t = null;
            //            using (SqlConnection conn = new SqlConnection(connectionString))
            //            {
            //                using (SqlCommand cmd = conn.CreateCommand())
            //                {
            //                    cmd.CommandText =  @"SELECT [PaymentID]
            //                                          ,[TransDate]
            //                                          ,[PaymentNo]
            //                                          ,[PaymentDate]
            //                                          ,[PaymentValue]
            //                                          ,[InvoiceNo]
            //                                          ,[DebtorSectorID]
            //                                          ,[PostingDate]
            //                                          ,[PostingBy]
            //                                          ,[CreatedDate]
            //                                          ,[CreatedBy]
            //                                          ,[UpdatedDate]
            //                                          ,[UpdatedBy]
            //                                          ,[Remark]
            //                                          ,[IsVerif]
            //                                      FROM [OJK Receivables].[transactions].[recPayment]
            //                                      where PaymentNo = '" + recpayment.PaymentNo + "'";
            //                    cmd.CommandType = CommandType.Text;
            //                    try
            //                    {
            //                        conn.Open();
            //                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            //                        {
            //                            while (dr.Read())
            //                            {
            //                                t = new recPayment(true);
            //                                t.PaymentID = dr[recPayment.Fields.PaymentID] == DBNull.Value ? 0 : int.Parse(dr[recPayment.Fields.PaymentID].ToString());
            //                                t.TransDate = dr[recPayment.Fields.TransDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recPayment.Fields.TransDate].ToString());
            //                                t.PaymentNo = dr[recPayment.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recPayment.Fields.PaymentNo].ToString();
            //                                t.PaymentDate = dr[recPayment.Fields.PaymentDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recPayment.Fields.PaymentDate].ToString());
            //                                t.PaymentValue = dr[recPayment.Fields.PaymentValue] == DBNull.Value ? 0 : decimal.Parse(dr[recPayment.Fields.PaymentValue].ToString());
            //                                t.InvoiceNo = dr[recPayment.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recPayment.Fields.InvoiceNo].ToString();
            //                                t.DebtorSectorID = dr[recPayment.Fields.DebtorSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recPayment.Fields.DebtorSectorID].ToString());
            //                                t.PostingDate = dr[recPayment.Fields.PostingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recPayment.Fields.PostingDate].ToString());
            //                                t.PostingBy = dr[recPayment.Fields.PostingBy] == DBNull.Value ? string.Empty : dr[recPayment.Fields.PostingBy].ToString();
            //                                t.CreatedDate = dr[recPayment.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recPayment.Fields.CreatedDate].ToString());
            //                                t.CreatedBy = dr[recPayment.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recPayment.Fields.CreatedBy].ToString();
            //                                t.UpdatedDate = dr[recPayment.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recPayment.Fields.UpdatedDate].ToString());
            //                                t.UpdatedBy = dr[recPayment.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recPayment.Fields.UpdatedBy].ToString();

            //                                t.recDebtorSector = new recDebtorSector(true);
            //                                t.recDebtorSector.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
            //                                t.recDebtorSector.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
            //                                t.recDebtorSector.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
            //                                t.recDebtorSector.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();

            //                                t.recInvoice.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? 0 : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
            //                                t.Dispose();
            //                            }
            //                        }
            //                    }
            //                    catch (Exception ex)
            //                    {
            //                        throw (ex);
            //                    }
            //                    finally
            //                    {
            //                        if (conn.State == ConnectionState.Open) conn.Close();
            //                    }
            //                }
            //            }
            #endregion
            #region Update To Verify
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    foreach (string i in invoiceNo)
                    {
                        cmd.CommandText = @"UPDATE [OJK Receivables].[transactions].[recPayment]
                                        SET [IsVerif] = 1
                                        WHERE InvoiceNo = '" + i + "'";
                        cmd.CommandType = CommandType.Text;

                        try
                        {
                            conn.Open();
                            cmd.ExecuteNonQuery();
                            resultSucess.Add(i);
                        }
                        catch (Exception ex)
                        {
                            throw (ex);
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open) conn.Close();
                        }
                    }
                }
            }
            #endregion
            return resultSucess;
        }

        /// <summary>
        /// List ListUnverifiedPayment diambil dari  this.GetList dgn menambahkan pada parameter : WHERE transactions.recPayment.IsVerif = 1
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="currentPage"></param>
        /// <param name="sortName"></param>
        /// <param name="sortOrder"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable ListUnverifiedPayment(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            #region UnverifiedList
            string appendParam = string.Empty;

            DataTable dt = new DataTable();
            if (parameters == null || parameters == "" || parameters == string.Empty)
                appendParam = " where [transactions].[recPayment].[IsVerif] is null or [transactions].[recPayment].[IsVerif] = 0";
            else
                appendParam = parameters + "  AND [transactions].[recPayment].[IsVerif] is null or [transactions].[recPayment].[IsVerif] = 0";


            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"     master.recDebtor.DebtorID AS _DebtorID, master.recDebtor.DebtorName AS NamaPerusahaan, master.recSector.SectorID AS _SectorID, 
                      master.recSector.SectorName AS NamaSector, transactions.recInvoice.InvoiceNo AS NoInvoice, transactions.recInvoice.InvoiceValue AS JumlahTagihan, 
                      transactions.recPayment.PaymentNo AS IDTransaksi, transactions.recPayment.PaymentValue AS JumlahPembayaran, 
                      transactions.recBilling.DueDate AS TanggalJatuhTempo
");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"     master.recDebtorSector INNER JOIN
                      transactions.recPayment ON master.recDebtorSector.DebtorSectorID = transactions.recPayment.DebtorSectorID INNER JOIN
                      master.recDebtor ON master.recDebtorSector.DebtorID = master.recDebtor.DebtorID INNER JOIN
                      master.recSector ON master.recDebtorSector.SectorID = master.recSector.SectorID INNER JOIN
                      transactions.recInvoice ON transactions.recPayment.InvoiceNo = transactions.recInvoice.InvoiceNo INNER JOIN
                      transactions.recBilling ON transactions.recInvoice.InvoiceNo = transactions.recBilling.InvoiceNo

                    ");
                    sbQuery.AppendFormat("      {0} ", appendParam);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }


                    
                }
            }
            return dt;

            #endregion
        }

        /// <summary>
        /// SelectScalarListUnverifiedPayment(SQL.Function.Aggregate function, string column, string parameters)
        /// </summary>
        /// <param name="function"></param>
        /// <param name="column"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object SelectScalarListUnverifiedPayment(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            string appendParam = string.Empty;

            if (parameters == null || parameters == "" || parameters == string.Empty)
                appendParam = " where [transactions].[recPayment].[IsVerif] is null or [transactions].[recPayment].[IsVerif] = 0";
            else
                appendParam = parameters + "  AND [transactions].[recPayment].[IsVerif] is null or [transactions].[recPayment].[IsVerif] = 0";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recPayment {1}", column, appendParam);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recPayment {1}", column, appendParam);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recPayment {1}", column, appendParam);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recPayment {1}", column, appendParam);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recPayment {1}", column, appendParam);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recPayment {1}", column, appendParam);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        /// <summary>
        /// Get All bank Account 
        /// </summary>
        /// <returns></returns>
        public List<recBankAccount> ListRecBankAccount()
        {
            List<recBankAccount> tt = new List<recBankAccount>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT [BankID]
                                              ,[BankCode]
                                              ,[BankName]
                                              ,[AccountCode]
                                              ,[AccountName]
                                              ,[Address1]
                                              ,[Address2]
                                              ,[Address3]
                                              ,[IsActive]
                                              ,[CreatedDate]
                                              ,[CreatedBy]
                                              ,[UpdatedDate]
                                              ,[UpdatedBy]
                                          FROM [OJK Receivables].[master].[recBankAccount]";
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBankAccount t = new recBankAccount();
                                t.BankID = dr[recBankAccount.Fields.BankID] == DBNull.Value ? (short)0 : short.Parse(dr[recBankAccount.Fields.BankID].ToString());
                                t.BankCode = dr[recBankAccount.Fields.BankCode] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.BankCode].ToString();
                                t.BankName = dr[recBankAccount.Fields.BankName] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.BankName].ToString();
                                t.AccountCode = dr[recBankAccount.Fields.AccountCode] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.AccountCode].ToString();
                                t.AccountName = dr[recBankAccount.Fields.AccountName] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.AccountName].ToString();
                                t.Address1 = dr[recBankAccount.Fields.Address1] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.Address1].ToString();
                                t.Address2 = dr[recBankAccount.Fields.Address2] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.Address2].ToString();
                                t.Address3 = dr[recBankAccount.Fields.Address3] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.Address3].ToString();
                                t.IsActive = dr[recBankAccount.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recBankAccount.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recBankAccount.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBankAccount.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recBankAccount.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recBankAccount.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBankAccount.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recBankAccount.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        /// <summary>
        /// ListDataPungutan() Isikan Counter true untuk Filter By Date, false untuk Filter by Month
        /// </summary>
        /// <param name="TglAwal"></param>
        /// <param name="TglAkhir"></param>
        /// <param name="Bulan"></param>
        /// <param name="ConterFilter"></param>
        /// <returns></returns>
        public List<LoadDataPungutanDTO> ListDataPungutan(DateTime TglAwal, DateTime TglAkhir, int Bulan, bool CounterFilter)
        {
            DataTable dt = new DataTable();
            if (CounterFilter)
            {
                #region Filter by Date

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = @"SELECT     transactions.recPayment.InvoiceNo AS [Pungutan_[ID Transaksi]]], master.recDebtor.DebtorName AS Pungutan_Debitur, 
                      transactions.recBilling.ForPayment AS Pungutan_Keterangan, transactions.recPayment.PaymentValue AS Pungutan_Jumlah, 
                      transactions.recPayment.PaymentDate AS Pungutan_PaymentDate
FROM         transactions.recPayment INNER JOIN
                      transactions.recInvoice INNER JOIN
                      transactions.recBilling ON transactions.recInvoice.InvoiceNo = transactions.recBilling.InvoiceNo INNER JOIN
                      master.recDebtor INNER JOIN
                      master.recDebtorSector ON master.recDebtor.DebtorID = master.recDebtorSector.DebtorID INNER JOIN
                      master.recSector ON master.recDebtorSector.SectorID = master.recSector.SectorID ON 
                      transactions.recBilling.DebtorSectorID = master.recDebtorSector.DebtorSectorID ON transactions.recPayment.InvoiceNo = transactions.recInvoice.InvoiceNo
                      where    transactions.recPayment.InvoiceNo in  (
									SELECT [InvoiceNo]
									FROM [OJK Receivables].[transactions].[recLoadReconDetail]
									where InvoiceNo is not null) AND ( transactions.recPayment.PaymentDate >= @TglAwal AND  transactions.recPayment.PaymentDate <= @TglAkhir )";

                        cmd.Parameters.Add("@TglAwal", SqlDbType.DateTime).Value = TglAwal;
                        cmd.Parameters.Add("@TglAkhir", SqlDbType.DateTime).Value = TglAkhir;
                        cmd.CommandType = CommandType.Text;

                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = cmd;

                        try
                        {
                            conn.Open();
                            da.Fill(dt);
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open) conn.Close();
                        }
                    }
                }

                #endregion
            }
            else
            {
                #region Filter by Month

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = @"SELECT     transactions.recPayment.InvoiceNo AS [Pungutan_[ID Transaksi]]], master.recDebtor.DebtorName AS Pungutan_Debitur, 
                      transactions.recBilling.ForPayment AS Pungutan_Keterangan, transactions.recPayment.PaymentValue AS Pungutan_Jumlah, 
                      transactions.recPayment.PaymentDate AS Pungutan_PaymentDate
FROM         transactions.recPayment INNER JOIN
                      transactions.recInvoice INNER JOIN
                      transactions.recBilling ON transactions.recInvoice.InvoiceNo = transactions.recBilling.InvoiceNo INNER JOIN
                      master.recDebtor INNER JOIN
                      master.recDebtorSector ON master.recDebtor.DebtorID = master.recDebtorSector.DebtorID INNER JOIN
                      master.recSector ON master.recDebtorSector.SectorID = master.recSector.SectorID ON 
                      transactions.recBilling.DebtorSectorID = master.recDebtorSector.DebtorSectorID ON transactions.recPayment.InvoiceNo = transactions.recInvoice.InvoiceNo
                      where    transactions.recPayment.InvoiceNo in  (
									SELECT [InvoiceNo]
									FROM [OJK Receivables].[transactions].[recLoadReconDetail]
									where InvoiceNo is not null) AND month(transactions.recPayment.PaymentDate ) = @bulan ";

                        cmd.Parameters.Add("@bulan", SqlDbType.Int).Value = Bulan;
                        cmd.CommandType = CommandType.Text;

                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = cmd;

                        try
                        {
                            conn.Open();
                            da.Fill(dt);
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open) conn.Close();
                        }
                    }
                }


                #endregion
            }

            List<LoadDataPungutanDTO> LoadDataPungutanDTOs = new List<LoadDataPungutanDTO>();

            foreach (DataRow dr in dt.Rows)
            {
                LoadDataPungutanDTO dto = new LoadDataPungutanDTO();


                dto.Pungutan_IDTransaksi = dr[0] == DBNull.Value ? "" : dr[0].ToString();
                dto.Pungutan_Debitur = dr[1] == DBNull.Value ? "" : dr[1].ToString();
                dto.Pungutan_Keterangan = dr[2] == DBNull.Value ? "" : dr[2].ToString();
                dto.Pungutan_Jumlah = dr[3] == DBNull.Value ? 0 : (decimal)(dr[3]);
                dto.Pungutan_PaymentDate = dr[4] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[4].ToString());

                LoadDataPungutanDTOs.Add(dto);
            }

            return LoadDataPungutanDTOs;
        }

        /// <summary>
        /// ListDataBank() Isikan Counter true untuk Filter By Date, false untuk Filter by Month
        /// </summary>
        /// <param name="TglAwal"></param>
        /// <param name="TglAkhir"></param>
        /// <param name="Bulan"></param>
        /// <param name="CounterFilter"></param>
        /// <returns></returns>
        public List<LoadDataBankDTO> ListDataBank(DateTime TglAwal, DateTime TglAkhir, int Bulan, bool CounterFilter)
        {
            DataTable dt = new DataTable();
            if (CounterFilter)
            {
                #region Filter by Date

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = @"SELECT      transactions.recLoadReconDetail.InvoiceNo AS [Data_Bank_[ID Transaksi]]], transactions.recLoadReconDetail.TransNote AS Data_Bank_Keterangan, 
                                                  transactions.recLoadReconDetail.MutationValue AS Data_Bank_Jumlah, transactions.recLoadReconDetail.TransDate AS Data_Bank_TransDate
                                            FROM         transactions.recLoadRecon right outer JOIN
                                                  transactions.recLoadReconDetail ON transactions.recLoadRecon.LoadReconID = transactions.recLoadReconDetail.LoadReconID left outer JOIN
                                                  transactions.recAdjus ON transactions.recLoadReconDetail.TransID = transactions.recAdjus.TransBankId
                                                  where transactions.recLoadReconDetail.InvoiceNo is not null and (  transactions.recLoadReconDetail.TransDate >= @TglAwal AND  transactions.recLoadReconDetail.TransDate <= @TglAkhir ) ";

                        cmd.Parameters.Add("@TglAwal", SqlDbType.DateTime).Value = TglAwal;
                        cmd.Parameters.Add("@TglAkhir", SqlDbType.DateTime).Value = TglAkhir;
                        cmd.CommandType = CommandType.Text;

                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = cmd;

                        try
                        {
                            conn.Open();
                            da.Fill(dt);
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open) conn.Close();
                        }
                    }
                }

                #endregion
            }
            else
            {
                #region Filter by Month

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = @"SELECT      transactions.recLoadReconDetail.InvoiceNo AS [Data_Bank_[ID Transaksi]]], transactions.recLoadReconDetail.TransNote AS Data_Bank_Keterangan, 
                                                  transactions.recLoadReconDetail.MutationValue AS Data_Bank_Jumlah, transactions.recLoadReconDetail.TransDate AS Data_Bank_TransDate
                                            FROM         transactions.recLoadRecon right outer JOIN
                                                  transactions.recLoadReconDetail ON transactions.recLoadRecon.LoadReconID = transactions.recLoadReconDetail.LoadReconID left outer JOIN
                                                  transactions.recAdjus ON transactions.recLoadReconDetail.TransID = transactions.recAdjus.TransBankId
                                                  where transactions.recLoadReconDetail.InvoiceNo is not null and   month(transactions.recLoadReconDetail.TransDate )  = @bulan ";

                         cmd.Parameters.Add("@bulan", SqlDbType.Int).Value = Bulan;

                        cmd.CommandType = CommandType.Text;

                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = cmd;

                        try
                        {
                            conn.Open();
                            da.Fill(dt);
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open) conn.Close();
                        }
                    }
                }


                #endregion
            }


            List<LoadDataBankDTO> LoadDataBankDTOs = new List<LoadDataBankDTO>();

            foreach (DataRow dr in dt.Rows)
            {
                LoadDataBankDTO dto = new LoadDataBankDTO();


                dto.Data_Bank_IDTransaksi = dr[0] == DBNull.Value ? "" : dr[0].ToString();
                dto.Data_Bank_Keterangan = dr[1] == DBNull.Value ? "" : dr[1].ToString();
                dto.Data_Bank_Jumlah = dr[2] == DBNull.Value ? 0 : Convert.ToDecimal(dr[2].ToString());
                dto.Data_Bank_TransDate = dr[3] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[3].ToString());

                LoadDataBankDTOs.Add(dto);
            }

            return LoadDataBankDTOs;

        }

        /// <summary>
        /// ListDataPungutanDanBankRekonsiliasi() adalah populte dari ListDataBank() dan ListDataPungutan()
        /// </summary>
        /// <param name="TglAwal"></param>
        /// <param name="TglAkhir"></param>
        /// <param name="Bulan"></param>
        /// <param name="CounterFilter"></param>
        /// <returns></returns>
        public List<LoadDataReconsiliasiDTO> ListDataPungutanDanBankRekonsiliasi(DateTime TglAwal, DateTime TglAkhir, int Bulan, bool CounterFilter)
        {
            List<LoadDataBankDTO> LoadDataBankDTOs = new List<LoadDataBankDTO>();
            List<LoadDataPungutanDTO> LoadDataPungutanDTOs = new List<LoadDataPungutanDTO>();
            List<LoadDataReconsiliasiDTO> LoadDataReconsiliasiDTOs = new List<LoadDataReconsiliasiDTO>(); 

            LoadDataPungutanDTOs = this.ListDataPungutan(TglAwal, TglAkhir, Bulan, CounterFilter);
            LoadDataBankDTOs = this.ListDataBank(TglAwal, TglAkhir, Bulan, CounterFilter);


            foreach (LoadDataBankDTO bank in LoadDataBankDTOs)
            {
                LoadDataPungutanDTO pungli = new LoadDataPungutanDTO();
                LoadDataReconsiliasiDTO recon = new LoadDataReconsiliasiDTO();

                pungli = LoadDataPungutanDTOs.Where(x => x.Pungutan_IDTransaksi.Trim().Equals(bank.Data_Bank_IDTransaksi.Trim())).FirstOrDefault();

                if (pungli != null)
                {
                    recon.Pungutan_IDTransaksi = pungli.Pungutan_IDTransaksi == null ? "" : pungli.Pungutan_IDTransaksi;
                    recon.Pungutan_Debitur = pungli.Pungutan_Debitur == null ? "" : pungli.Pungutan_Debitur;
                    recon.Pungutan_Keterangan = pungli.Pungutan_Keterangan == null ? "" : pungli.Pungutan_Keterangan;
                    recon.Pungutan_Jumlah = pungli.Pungutan_Jumlah == null ? 0 : pungli.Pungutan_Jumlah;
                    recon.Pungutan_PaymentDate = pungli.Pungutan_PaymentDate == null ? (DateTime?)null : pungli.Pungutan_PaymentDate;
                }

                recon.Data_Bank_IDTransaksi = bank.Data_Bank_IDTransaksi == null ? "" : bank.Data_Bank_IDTransaksi;
                recon.Data_Bank_Jumlah = bank.Data_Bank_Jumlah == null ? 0 : bank.Data_Bank_Jumlah;
                recon.Data_Bank_Keterangan = bank.Data_Bank_Keterangan == null ? "" : bank.Data_Bank_Keterangan;
                recon.Data_Bank_TransDate = bank.Data_Bank_TransDate == null ? (DateTime?)null : bank.Data_Bank_TransDate;

                recon.Status = this.CheckInvoiceRecon(recon.Data_Bank_IDTransaksi);

                if (recon.Status.ToLower() == "penyesuaian")
                {
                    recLoadReconDetail recLoadReconDetail = new recLoadReconDetail();
                    recLoadReconDetailRepo recLoadReconDetailRepo = new recLoadReconDetailRepo(connectionString);
                    recLoadReconDetail = recLoadReconDetailRepo.GetByInvoiceNumber(recon.Data_Bank_IDTransaksi);


                    if (this.CheckDataOnAdjustmentByTransID(recLoadReconDetail.TransID) == false)
                        LoadDataReconsiliasiDTOs.Add(recon);
                }
                else
                {
                    LoadDataReconsiliasiDTOs.Add(recon);
                }
            }

            return LoadDataReconsiliasiDTOs;
        }

        /// <summary>
        /// Pengecekan apakah Object sudah terdpat di Adjusment
        /// </summary>
        /// <param name="TransID"></param>
        /// <returns></returns>
        public bool CheckDataOnAdjustmentByTransID(string TransID)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string getInvoice = @" select * from 
									[OJK Receivables].[transactions].[recLoadReconDetail]
									where TransID =@TransID";
                    cmd.Parameters.Add("@TransID", SqlDbType.VarChar).Value = TransID;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = getInvoice;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// Untuk mendapatkan 3 data Invoice dari 3 table : RECONDetail, Invoice, payment
        /// </summary>
        /// <param name="InvoiceNo"></param>
        /// <returns></returns>
        public string CheckInvoiceRecon(string InvoiceNo)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string getInvoice = @" SELECT     transactions.recLoadReconDetail.InvoiceNo AS [RECON.INVO], transactions.recInvoice.InvoiceNo AS [INVO.INVO] , transactions.recPayment.InvoiceNo AS [PAYMN.INVO]
                                        FROM         transactions.recLoadReconDetail  LEFT OUTER JOIN
                                                              transactions.recInvoice ON transactions.recLoadReconDetail.InvoiceNo = transactions.recInvoice.InvoiceNo
                                                               LEFT OUTER JOIN
                                                              transactions.recPayment ON transactions.recInvoice.InvoiceNo = transactions.recPayment.InvoiceNo 
                                        WHERE     transactions.recLoadReconDetail.InvoiceNo=@InvoiceNo";


                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = InvoiceNo;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = getInvoice;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            string ReconINVO = "";
            string InvoINVO = "";
            string PaymentINVO = "";

            foreach (DataRow row in dt.Rows)
            {
                ReconINVO = row[0] == DBNull.Value ? "" : row[0].ToString();
                InvoINVO = row[1] == DBNull.Value ? "" : row[1].ToString();
                PaymentINVO = row[2] == DBNull.Value ? "" : row[2].ToString();
            }

            //if (ReconINVO != "")
            //{
                if (ReconINVO == "" && InvoINVO == "" && PaymentINVO == "")
                    return "Penyesuaian";
                else if (ReconINVO == InvoINVO && ReconINVO != PaymentINVO)
                    return "UpdatePayment";
                else return "";
            //}
            //else
            //    return "";

        }
    }
}
